FRIHOST FORUMS SEARCH FAQ TOS BLOGS COMPETITIONS
You are invited to Log in or Register a free Frihost Account!


MySQL Error in code but works in phpMyAdmin





welshsteve
Hi everyone.

I am building an admin area for a website I'm building. It's for a sports league and I have a script to add a team to the table that holds the league division details (league table). At the same time, it creates a table for that team to hold player stats. This is all working fine. I am now trying to create a deletion script that removes the team from the league table, but also removes the database table for the player stats.

Here is the deletion page script:
Code:

<?php
$con = mysql_connect("SITE","USER","PASSWORD");
if (!$con)
  {
  die('Could not connect: ' . mysql_error() . '<br /><br />Try again later');
  }

mysql_select_db("DATABASE", $con);

$id = $_POST['id'];
$team = $_POST['team'];
$theteam = str_replace(" ","_",$team);

$sql="DROP TABLE `Stats_W_$theteam`;
DELETE FROM Div_Prelim WHERE Div_Prelim.id = $id;";

echo "<br />".$sql."<br />";

if (!mysql_query($sql,$con))
  {
  die('Error: ' . mysql_error());
  }

echo "<p>Team removed from table.</p>";
echo "<p><a href='tab_wp_del_p.php'>Delete Another</a>";
echo "<p><a href='admin.php'>Admin Home</a>";

mysql_close($con);

?>


This gives me the following error:

DROP TABLE `Stats_W_A_Test_Team`; DELETE FROM Div_Prelim WHERE Div_Prelim.id = 37;
Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DELETE FROM Div_Prelim WHERE Div_Prelim.id = 37' at line 2

Now my problem is that although this gives me the error, if I copy the SQL statement the script produces into phpMyAdmin and execute it, the script works and does the deletions correctly.

Does anyone know why it works in phpMyAdmin, but won't work with my script? It's doing my head in Sad Sad Embarassed Mad Crying or Very sad

UPDATE: OK, so I split the SQL statements up and run them separately in the page, and it worked. WEIRD!!!
Fire Boar
It's not that weird. PHP's mysql_query performs queries one at a time, just like the mysql console does. The console uses semicolon to signal "end of query". In PHP, you just call mysql_query once per query, the semicolon doesn't actually mean anything to the mysql backend and so returns an error.

In phpMyAdmin, semicolon-separated statements are parsed to allow multiple queries to be executed sequentially. This is useful for - say - restoring a database dump. All phpMyAdmin does is split on the semicolons and query the database for each item in the split.
Related topics
How To : Improve Your PHP Programming
have problem on my sql
PHP DB Error: syntax error
MySQL statement error (got me going)
Mysql error!
[mySQL] Error I cannot Debug
MySql error: Column count doesn't match value count at row 1
MySQL Data Not Inserting - I keep getting an error
SQL Insert
MySQL error
MySQL error
after server 4 crash I have a 500 error code
MySQL Error: Timestamp
memory issue error code 0123
Reply to topic    Frihost Forum Index -> Scripting -> Php and MySQL

FRIHOST HOME | FAQ | TOS | ABOUT US | CONTACT US | SITE MAP
© 2005-2011 Frihost, forums powered by phpBB.