mysql_query problem

Anon

I am stumped with the code below. It is designed to create a table within a MySQL database called "Products". Creating the connection and selecting the database goes off without a hitch. However, I always get a "Couldn't execute query" error when I arrive at that stage. Any help would be greatly appreciated.

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">

<HEAD>
<TITLE>Create a Database Table: Step 3</TITLE>
</HEAD>
<BODY>
<H1>Adding Table <?php echo "$table_name"; ?></h1>
<?php
$sql = "CREATE TABLE $table_name (";
for ($i = 0; $i < count($field_name); $i++) {
$sql .= "$field_name[$i] $field_type[$i]";
if ($field_length[$i] != "") {
$sql .= " ($field_length[$i]),";
} else {
$sql .= ",";
}
}
$sql = substr($sql, 0, -1);
$sql .= ")";

// create connection
$connection = mysql_connect("localhost","sandman","password") or die("Couldn't connect to server.");

//select database
$db = mysql_select_db("Products",$connection) or die ("Couldn't select database.");

// execute SQL query and get result
$sql_result = mysql_query($sql,$connection) or die ("Couldn't execute query.");

  if (!$sql_result) {
      echo "<p>Couldn't create table!";
    } else {
  echo "<P>$table_name has been created!";
    }

?>
</BODY>

Anon

I just went though this myself, only to find that one of the column names I was using was actualy the same as a MySQL function name, took me 2 hours to find this error too cause it was one of the more obscure MySQL commands. I'd suggest checking that first.

It also might be becuase of a space or some other character that isn't allowed to be used in a column name, check all posiblities.

Either way always check your query by echoing before you send it, then copy and run that query directly either using mysql's commandline tools or some other MySQL admin tool like phpMyAdmin, if it works there then it's not the query that's causing the problem.

Hope this helps a little.

Anon

There can be few different explanations for your problem, but in most cases the error occurs when character, that isn't allowed to be used in coulmn or table names, has been used.

Slaven

Anon

Instead of making your script die with a useless error like "it didn't work",
use proper error reporting:

if (!($result=mysql_query($sql_statement,$db_connection)))
{

Query failed, report error

print mysql_errno().": ".mysql_error()."
";
}
else
{

Query ok, process output

};

Anon

Thanks to all...

Using the error reporting script  from Vincent helped me to isolate the problem. One of the input fields wasn't properly escaped.

Thanks again.

Anon

Or for better practice and to save on loads of redundant typing turn that into a simple function to use in conjunction with die().

~sample code~
<?php
function ShowMySQLError()
{
return mysql_errno().": ".mysql_error();
}

$result=mysql_query($sql_statement,$db_connection) or die(ShowMySQLError());
?>

Anon

Nice one, but this is not always what you want because a MySQL error is not allways a "die" event. (for example, inserting the same value into a primary key column twice does give you an error, but it is not a "die" event)

Anon

Well then you could just use the function after the 'or' statement instead of encasing it in a 'die' function you could just echo it, or add it to some global error array which you could later iiterate through and use in a more suitable place (which is what I commonly do). The idea of my post was simply to point out to the newbies that they could really simplify their code by doing such things.