Accessing SQL Database from PHP scripts

CNR

Hi All,

I am able to connect to a database on SQL server1 ,but none of my SQL queries seem to be working. The same queries work fine on a different SQL server, where i have used the SQL queries directly on the database and not though PHP script,as there is a problem connecting to this server.
Can i know why my SQL queries are not working on SQL server1, while the same queries work on a different server?Unfortunately i can access the Server1 database only through PHP scripts, so i cant know what is the error on the server. Please let me know. Below is the script.

[php]<HTML><head><title>Sql test</title></head>
<body>
<?php
$host = "xxxxxx.com";
$usr = "xxxx";
$pwd = "xxxxxxxxxxxx";
$cid = @mysql_connect($host,$usr,$pwd);
echo"$cid ";

$db = "chin";

$SQL = 'CREATE TABLE Photos (id int(6) unsigned NOT NULL auto_increment,filename varchar(100) NOT NULL default \",gallery varchar(100) NOT NULL default \",PRIMARY KEY (id)) TYPE=MyISAM';
$SQL2 = 'INSERT INTO Photos VALUES (1,\'PinkRose\',\'images\')';
$SQL3 = 'SELECT * FROM Photos ';

$result = @mysql_select_db($db,$cid);

$result2=mysql_query($SQL);

mysql_query($SQL2);
$result2 = @mysql_db_query($db,$SQL3,$cid);
$res3 = mysql_affected_rows();
mysql_close();

echo"$result :( ";
echo "$result2 ";
echo" res3= $res3 ";

?>
</body>
</html>[/php]
Thanks ,
CNR

ronverdonk

You don't get results from your query because you don't fetch any rows from the query result object. In order to get the rows you must issue a mysql_fetch_* command against that object. Also you should test for any error after a mysql_ command, only that way you can see what is going on when an error happens. See the following adapted part of your code:
[php]// CREATE the table
$result2=mysql_query($SQL)
or die("CREATE failed: ".mysql_error());
// INSERT some rows
result3=mysql_query($SQL2)
or die("INSERT failed: ".mysql_error());
// SELECT table columns
$result4 = mysql_query($QL3)
or die("SELECT failed: ".mysql_error());
// establish no of table columns in result
$num_fields=mysql_num_fields($result4);
// read each row in the result
while ($row=mysql_fetch_assoc($result4)) {
// print each column of each row
for ($i=0; $i < $num_fields; $i++)
echo $row[$i].' ';
echo ' ';
}
$res3 = mysql_affected_rows();
mysql_close();
echo" res3= $res3 ";[/php]
Ronald :cool:

CNR

Hi,

Thank you very much for your reply. It helped me know what the exact errors were. I could create the table but have some issues with insert.

The following query
INSERT INTO Photos2(id,filename,gallery) VALUES (2,\'PinkRose\',\'images\')
gives error -- Unknown column 'gallery' in 'field list'.

I am using the following query to create the table,but looks lilke the gallery column is not added to the table.
$SQL = "CREATE TABLE Photos2 (id int(6) unsigned NOT NULL auto_increment,filename varchar(100) NOT NULL default \",gallery varchar(100) NOT NULL default \",PRIMARY KEY (id)) TYPE=MyISAM";
Can i get some help with this?

Thanks

Accessing SQL Database from PHP scripts

re: Accessing SQL Database from PHP scripts

re: Accessing SQL Database from PHP scripts

Similar PHP bytes

/bytes/development

/bytes/it

/bytes/about