Would Like Some Advice

Creating a mysql database and would like advice on the setup

[webfirst]

I have been working on a database that needs to be updated frequently and easily. My client provides me with MSexcel spreadsheets that I convert to a php file that updates/creates databases and/or tables. That file is uploaded to the server and then run automatically through cron jobs on set schedule (immediately after a backup of current database). The system works fine but I believe it could use some improvements.

The site works on a static html platform that has imbedded php code that queries the database. The site contains some 10,000 products and is growing daily. That is why I need some input on the script and table setup.

Here is a sample of the converted php file that creates/updates the database tables:

<?php
//Increasing maximum exection time of the script
set_time_limit(120);
echo '<p>Connectiong to MySQL server <br>';
$dbName = '#*$!#*$!xx_#*$!#*$!#*$!x'; //database name
$userName = '#*$!#*$!xx_#*$!#*$!xx'; //User name
$password = '#*$!#*$!#*$!#*$!#*$!xx'; //Password
$localHost = 'localhost'; //Server
//Connecting to MySQL
if (!mysql_connect($localHost, $userName, $password))
{
echo '<p>Error while trying to connect to MySQL'.mysql_error() ;
exit ;
}
echo '<p>Connected!';
echo '<p>creating Database ..... <br>';
if (!mySQL_query("Create Database if not exists `$dbName`"))
{
echo '<p>Error creating the Databse '.mysql_error();
exit ;
}
if(!mysql_select_db($dbName))
die('<p>error selecting the database');
#-----creating tabel: Sheet1
$tabel = 'Sheet1'; //tabel name
if(!mySQL_query("DROP Table IF EXISTS `Sheet1`"))
die('<p>Error overwriting table:Sheet1'.mysql_error());
echo '<p>creating table Sheet1 <br>';
$sql ="CREATE TABLE IF NOT Exists `Sheet1`( `productid` varchar(255),`catagoryid` varchar(255),`sizeid` varchar(255),`productname` varchar(255),`price` varchar(255),
PRIMARY KEY (`productid`))";
if(!mySQL_query($sql))
{
echo '<p> Error creating tabel: Sheet1 '.mysql_error();
}
#-----populating Sheet1 Imaginary Products Table
echo '<p>populating Sheet1';
mysql_query("insert into `Sheet1` (`productid`,`catagoryid`,`sizeid`,`productname`,`price`) VALUES ( 68870 ,'sports rug' ,'s234' ,'AirForce' ,'$69.00' )");
mysql_query("insert into `Sheet1` (`productid`,`catagoryid`,`sizeid`,`productname`,`price`) VALUES ( 74210 ,'sports rug' ,'s200' ,'Alabama AM' ,'$89.00' )");
mysql_query("insert into `Sheet1` (`productid`,`catagoryid`,`sizeid`,`productname`,`price`) VALUES ( 74166 ,'sports rug' ,'s201' ,'University of Alabama' ,'$189.00' )");
mysql_query("insert into `Sheet1` (`productid`,`catagoryid`,`sizeid`,`productname`,`price`) VALUES ( 74167 ,'sports rug' ,'s234' ,'University of Alabama 2' ,'$69.00' )");
mysql_query("insert into `Sheet1` (`productid`,`catagoryid`,`sizeid`,`productname`,`price`) VALUES ( 74170 ,'sports rug' ,'s200' ,'University of Alabama Birmingham' ,'$89.00' )");
mysql_query("insert into `Sheet1` (`productid`,`catagoryid`,`sizeid`,`productname`,`price`) VALUES ( 74197 ,'sports rug' ,'s201' ,'Appalachian State' ,'$189.00' )");
mysql_query("insert into `Sheet1` (`productid`,`catagoryid`,`sizeid`,`productname`,`price`) VALUES ( 79539 ,'sports rug' ,'s234' ,'Arizona State' ,'$69.00' )");
mysql_query("insert into `Sheet1` (`productid`,`catagoryid`,`sizeid`,`productname`,`price`) VALUES ( 79540 ,'sports rug' ,'s200' ,'Arizona State 2' ,'$89.00' )");
echo '<p>Done';
?>

I would appreciate any input on how to improve the table setup to make things work faster and more efficiently. Each of the actual tables consist of around 600 to 800 rows.

[mcibor]

There are two things you can change:

1. use multiple inserts:
INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);
instead of
INSERT INTO tbl_name (a,b,c) VALUES(1,2,3);
INSERT INTO tbl_name (a,b,c) VALUES(4,5,6);
INSERT INTO tbl_name (a,b,c) VALUES(7,8,9);

2. Here's some info, you can find interesting:

[dev.mysql.com...]
[dev.mysql.com...]