1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

Reality Check Please

Discussion in 'MySQL' started by carl_in_florida, Oct 2, 2006.

  1. #1
    I have an access database (~32,000 entries 11 fields) that I would like to convert to a website using php and MySql. I have never created a php MySql site. I have manipulated WP, Joomla, Gallery, e107 but I have never actually written scripts.

    I do not need any kind of admin panel. The data will not be updated. I just need to be able to search it and display by category or individual entry.

    1. Is this to ambitious for a first time project?
    2. Any tutorials you can point to?
    3. If the answer is to run and get a professional what is a reasonable cost for this? Again, I don't need an admin panel. Just display the data.

    Thanks,

    carl
     
    carl_in_florida, Oct 2, 2006 IP
  2. iduchesne

    iduchesne Peon

    Messages:
    205
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    0
    #2

    With Access you can export the tables to single file text. Not sure exactly anymore on formatting... it's been so long since I worked with Access.

    come to think of it.. you need structured insert statements and I don't think Access exports to that... Try finding a tool that connects to access and to MySQL to "upsize" the data. There used to be such a tool from Access to Enterprise SQL.
     
    iduchesne, Oct 2, 2006 IP
  3. carl_in_florida

    carl_in_florida Active Member

    Messages:
    1,066
    Likes Received:
    63
    Best Answers:
    0
    Trophy Points:
    90
    #3
    Well . . . I think i will be able to convert the database to an excel spreadsheet then import it to MySql but I still don't know how to display or search the data.

    note: I THINK i can convert and import the data.
     
    carl_in_florida, Oct 2, 2006 IP
  4. khasmoth

    khasmoth Well-Known Member

    Messages:
    1,211
    Likes Received:
    96
    Best Answers:
    0
    Trophy Points:
    165
    #4
    If you need a help drop me a pm. ;)
     
    khasmoth, Oct 3, 2006 IP
  5. ThomasNederman

    ThomasNederman Peon

    Messages:
    112
    Likes Received:
    4
    Best Answers:
    0
    Trophy Points:
    0
    #5
    I use Access export function, you right click on the table in question, select export, then in the type of export you go to the option ODBC, you can download MySql ODBC from the MySql homepage, then you create a ODBC connection to your server, and transfere the Access DB to SQL. Very easy.
    Sometimes if you have Identify fields, you need to recreate the exported data (add the identity and Primary Key (ID) )

    Good luck
     
    ThomasNederman, Oct 4, 2006 IP
  6. mnemtsas

    mnemtsas Super Dud

    Messages:
    497
    Likes Received:
    40
    Best Answers:
    0
    Trophy Points:
    0
    #6
    Not an ambitious project at all. Export the data from access to csv (or to Excel then to csv) and then use phpmyadmin to suck it all into MySQL. A decent tute and you should be up and running in 8-12 hrs. A pro could do it from go to whoa in 2-4 hrs. Actually its a pretty neat first up project.
     
    mnemtsas, Oct 4, 2006 IP
  7. carl_in_florida

    carl_in_florida Active Member

    Messages:
    1,066
    Likes Received:
    63
    Best Answers:
    0
    Trophy Points:
    90
    #7
    I was just about to throw in the towel. could you point me to said tute? I may just post a job on rent a coder or one of those forums and see what it might cost. I got one offer by pm.
     
    carl_in_florida, Oct 4, 2006 IP
  8. rosytoes

    rosytoes Peon

    Messages:
    230
    Likes Received:
    4
    Best Answers:
    0
    Trophy Points:
    0
    #8
    If you have never programmed before or not that way inclined, then I suggest you get someone to do it.
     
    rosytoes, Oct 4, 2006 IP
  9. carl_in_florida

    carl_in_florida Active Member

    Messages:
    1,066
    Likes Received:
    63
    Best Answers:
    0
    Trophy Points:
    90
    #9
    After wasting even more of my time I am absolutely convinced that this is the best advise I have gotten. I am technically inclined but learning php just to save a couple bucks is just plain stupid. Especially when this would be such an easy project for someone who knows how.

    Where's that "waving the white flag of surrender" icon?
     
    carl_in_florida, Oct 4, 2006 IP
  10. livingearth

    livingearth Well-Known Member

    Messages:
    1,469
    Likes Received:
    83
    Best Answers:
    0
    Trophy Points:
    140
    #10
    The following code should get you started. Of course you will have to substituite in your fieldnames, username, password and database name. Just add additional lines for more fields etc...
    You will also need to create a form to pass the $searchterm variable to this script...

    <?php
    function DB_connect($db_name)
    {
    mysql_connect ("localhost", "username", "password") or die (mysql_error());
    mysql_select_db ($db_name) or die (mysql_error());
    }


    DB_connect ('YourDatabaseName');
    $result = mysql_query("SELECT * FROM YourTableName WHERE YourFieldName LIKE '$searchterm");
    $num_rows = mysql_num_rows($result);
    while ($row = mysql_fetch_array($result)) {
    $field1 = $row["YOURfield1"];
    $field2 = $row["YOURfield2"];
    $field3 = $row["YOURfield3"];
    if($field2)
    echo "<font face=tahoma size=-1><b>$field1</b><br>\n";
    }
    if($num_rows == "0")
    echo "<font face=tahoma size=-1><b>No results found</b><br>\n";
    mysql_close();
    ?>

    If this is of help to anyone pls feel free to show your appreciation :)
     
    livingearth, Oct 4, 2006 IP
  11. livingearth

    livingearth Well-Known Member

    Messages:
    1,469
    Likes Received:
    83
    Best Answers:
    0
    Trophy Points:
    140
    #11
    If you need me to do the form too just let me know...
     
    livingearth, Oct 4, 2006 IP
  12. carl_in_florida

    carl_in_florida Active Member

    Messages:
    1,066
    Likes Received:
    63
    Best Answers:
    0
    Trophy Points:
    90
    #12
    I get this

    here is the code

     
    carl_in_florida, Oct 4, 2006 IP
  13. mnemtsas

    mnemtsas Super Dud

    Messages:
    497
    Likes Received:
    40
    Best Answers:
    0
    Trophy Points:
    0
    #13
    You are doing this to save a few bucks? Well if you think this is the only time you will ever use PHP and you're doing it to save a few dollars I would get someone else to do it for you. However, if you believe you will use PHP again then it may be worth considering this project as a "loss leader" where you burn your time learning. However, on subsequent projects you could reap the rewards of this effort.

    I'd be happy to help you through this (if you want to) and you can contact me via msn on .

    Now, to your code:

    $result = mysql_query("SELECT * FROM db1 WHERE topics LIKE '$searchterm");

    You dont seem to have set a search term ($searchterm) and the quotations around the search term variable are wrong.
     
    mnemtsas, Oct 4, 2006 IP
  14. Mystique

    Mystique Well-Known Member

    Messages:
    2,579
    Likes Received:
    94
    Best Answers:
    2
    Trophy Points:
    195
    #14
    What for your need to convert a more reliable MS-Access database into MySQL?

    MySQL is the most targeted database for malicious injection, not to mention the continuos errors that cause when your are hosted on a site with limited connection and privileges :rolleyes:


    And get ready for the every day worst phpMyAdmin :eek:
     
    Mystique, Oct 4, 2006 IP
  15. The Critic

    The Critic Peon

    Messages:
    392
    Likes Received:
    7
    Best Answers:
    0
    Trophy Points:
    0
    #15
    Try putting an 'at' sign (@) in front of mysql_num_rows and mysql_fetch_array and see if that clears it up. Also, be sure to define $searchterm, as mentioned above. It isn't hard to guard against injection if you know what to do.
     
    The Critic, Oct 4, 2006 IP
  16. carl_in_florida

    carl_in_florida Active Member

    Messages:
    1,066
    Likes Received:
    63
    Best Answers:
    0
    Trophy Points:
    90
    #16
    the @ cleared up the error!

    ummm. Now I just get a blank page
     
    carl_in_florida, Oct 5, 2006 IP
  17. carl_in_florida

    carl_in_florida Active Member

    Messages:
    1,066
    Likes Received:
    63
    Best Answers:
    0
    Trophy Points:
    90
    #17
    OK, here is what I have. i now gives me a no results found message

    
    <html>
    <head>
    <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
    <title>Illustrations</title>
    </head>
    <body>
    <?php
    function DB_connect($<mydatabase>)
    {
    mysql_connect ("localhost", "user", "PW") or die ('I cannot connect to the database because: ' . mysql_error());
    mysql_select_db ("<database>")or die (mysql_error());
    }
    
    
    DB_connect ('<database>');
    $result = mysql_query("SELECT * FROM <table name> WHERE topic LIKE '$talent'");
    $num_rows = @mysql_num_rows($result);
    while ($row = @mysql_fetch_array($result)) {
    $field1 = $row["topics"];
    $field2 = $row["source"];
    $field3 = $row["ID"];
    if($field2)
    echo "<font face=tahoma size=-1><b>$field1</b><br>\n";
    }
    if($num_rows == "0")
    echo "<font face=tahoma size=-1><b>No results found</b><br>\n";
    mysql_close();
    ?>
    </body>
    </html>
    
    Code (markup):
    At least I know it is working - just no doing anything
     
    carl_in_florida, Oct 5, 2006 IP
  18. falcondriver

    falcondriver Well-Known Member

    Messages:
    963
    Likes Received:
    47
    Best Answers:
    0
    Trophy Points:
    145
    #18
    sorry, but thats a lot of bs. i had my own shopping cart done in asp/access done some years ago, and it was always waving the white flag at some hours when i had a lot of page views, just because the file is locked if another user makes a query. an access.mdb is NOT a database, its a file and it dosnt comes even close to the number of connections it can handle compared to mysql.
    and you can get malicious injection with every database, it has nothing to do with mysql, access, mssql or whatever - just check your string before it goes to your db and youre fine.

    try this:
    function DB_connect($database)
    {
    mysql_connect ("localhost", "user", "PW") or die ('I cannot connect to the database because: ' . mysql_error());
    mysql_select_db ($database)or die (mysql_error());
    }
    Code (markup):
    please note that i removed this <> arround your variable $database.
    dont change anything here except your connection properties. replace the "user" and "PW" with your own username and password, make sure you keep the " arround because its a string here, not a variable (this are the words starting with an $ and without any single or double quotes)

    DB_connect ("db1");
    $sql = "SELECT * FROM mytable WHERE topic LIKE 'talent'";
    $result = mysql_query($sql);
    Code (markup):
    here you call the function DB_connect() as defined above, your variable $database has the value db1 here (assume this is your db since you mentioned that somewhere else). and i fixed your sql string. if it dosnt works, insert
    echo $sql."<br>";
    Code (markup):
    before $result = ... to see what youre trying to execute.

    if you dont like phpmyadmin then try http://www.navicat.com/ , has a working import function for access as well.
    phpmyadmin is not the number one choise to query your data during testing, but quite nice if your db is online and you have to do some small changes (adjusting taxes or delivery costs on a shopping systems, stuff like that).
     
    falcondriver, Oct 5, 2006 IP
  19. carl_in_florida

    carl_in_florida Active Member

    Messages:
    1,066
    Likes Received:
    63
    Best Answers:
    0
    Trophy Points:
    90
    #19
    MyPHPadmin comes with the hosting. I am on a shared server.

    When I originally posted this I was working off of xampp running on my local box. Now I am working off of my server.

    When i use the updated code Here is what the page displays

    Illustrations is the name of the table in the database.

    here is the whole code:

    <?php 
    function DB_connect($database)
    {
    mysql_connect ("localhost", "me", "mypw") or die 
    ('I cannot connect to the database because: ' . mysql_error());
    mysql_select_db ($database)or die (mysql_error());
    }
    DB_connect ("My-database-name");
    $sql = "SELECT * FROM Illustrations WHERE topics LIKE 'talent'";
    $result = mysql_query($sql);
    echo $sql."<br>";
    ?>
    Code (markup):
    Illustrations is the name of the table. am I missing something? When I look at the table in My PHPadmin it is all there nice and pretty. I don't understand.
     
    carl_in_florida, Oct 5, 2006 IP
  20. mnemtsas

    mnemtsas Super Dud

    Messages:
    497
    Likes Received:
    40
    Best Answers:
    0
    Trophy Points:
    0
    #20
    Well you are only going to echo out the sql code there. YOu should be trying to echo out the records now:
    
    if ($result)
    {
      if (mysql_num_rows($result)>0)
      {
        for ($i=0;$i<mysql_num_rows($result);$i++)
       {
    	  $row=mysql_fetch_assoc($result);
               echo $row['field_name1']." ".$row['field_name2']."<br />";
       }
      }
    }
    
    Code (markup):
     
    mnemtsas, Oct 5, 2006 IP