Reality Check Please

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

 

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.

 

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.

 

If you need a help drop me a pm.

 

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

 

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.

 

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.

 

If you have never programmed before or not that way inclined, then I suggest you get someone to do it.

 

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?

 

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

 

If you need me to do the form too just let me know...

 

I get this

here is the code

 

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.

 

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


And get ready for the every day worst phpMyAdmin

 

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 @ cleared up the error!

ummm. Now I just get a blank page

 

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

 

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).

 

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.

 

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):