Page 1 of 1

MySQL Error.

Posted: Wed Nov 22, 2006 5:40 pm
by Dirge of Cerberus
I was adapting a script to add a table into a database and store it in an index table. When I tried to run it, this happened.

Code: Select all

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/content/a/n/i/animestome/html/admin/addseries_test.php on line 34
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 'table) VALUES('Dattebayo','tester')' at line 1
Here's the original script:

Code: Select all

<?PHP require('incSession.php'); ?>

<center>
<?php
// set your infomation.
$dbhost='mysql23.secureserver.net';
$dbusername='***********';
$dbuserpass='*******';
$dbname='***********';
// connect to the mysql database server.
mysql_connect ($dbhost, $dbusername, $dbuserpass);
mysql_select_db($dbname) or die("Cannot select database");

//Are they just getting here or submitting their info?
if (isset($_POST["name"])) {
$name = $_POST["name"];
$size = $_POST["size"];
$url = $_POST["URL"];
$type = $_POST["type"];
//Was a field left blank?
if($name==NULL|$url==NULL) {
echo "All fields must be filled in.";
}else{
//Has the username or email been used?
$checkname = mysql_query("SELECT name FROM naruto WHERE name='$name'");
$name_exist = mysql_num_rows($checkname);

$checkurl = mysql_query("SELECT URL FROM naruto WHERE URL='$url'");
$url_exist = mysql_num_rows($checkurl);

if ($url_exist>0|$name_exist>0) {
echo "The name or URL of your anime is already in use";
}else{
//Everything seems good, lets insert.
$query = "INSERT INTO naruto (name, size, URL, type) VALUES('$name','$size','$url','$type')";
mysql_query($query) or die(mysql_error());
echo "The anime episode $name has been successfully registered.";
}
}
}
?>
<head>
<link rel="shortcut icon" href="../favicon.ico" />
<link rel="stylesheet" href="style_mini.css" type="text/css">
</head>
<h1>Register your Naruto Episode</h1>
<form action="narutoreg.php" method="POST">


  <table width=200 height="59" border=0 cellpadding=3 cellspacing=0>
    <tr>

      <td class="top">Anime Name</td>
    </tr>
    <tr>
      <td class="content"><input type="text" size="20" maxlength="25" name="name"></td>
    </tr>
    <tr>
      <td height="19" class="bottom">&nbsp;</td>
    </tr>

  </table>
<p>
  <table width=200 height="59" border=0 cellpadding=3 cellspacing=0>
    <tr>

      <td class="top">Size (MB)</td>
    </tr>
    <tr>
      <td class="content"><input type="text" size="20" maxlength="25" name="size"></td>
    </tr>
    <tr>
      <td height="19" class="bottom">&nbsp;</td>
    </tr>

  </table>
  <p>
    <table width=200 height="59" border=0 cellpadding=3 cellspacing=0>
    <tr>

      <td class="top">URL</td>
    </tr>
    <tr>
      <td class="content"><input type="text" size="20" maxlength="70" name="URL">
</td>
    </tr>
    <tr>
      <td height="19" class="bottom">&nbsp;</td>
    </tr>

  </table>
  <p>
    <table width=200 height="59" border=0 cellpadding=3 cellspacing=0>
    <tr>

      <td class="top">Type</td>
    </tr>
    <tr>
      <td class="content"><input type="text" size="6" maxlength="25" name="type" value="AVI"></td>
    </tr>
    <tr>
      <td height="19" class="bottom">&nbsp;</td>
    </tr>

  </table>
  <p>
      <table width=200 height="59" border=0 cellpadding=3 cellspacing=0>
    <tr>

      <td class="top">Submit</td>
    </tr>
    <tr>
      <td class="content"><center><input type="submit" value="Enter the Anime into the DB"></center></td>
    </tr>
    <tr>
      <td height="19" class="bottom">&nbsp;</td>
    </tr>

  </table>
</form>
</center>
<p><center>

  <table width=200 height="87" border=0 cellpadding=3 cellspacing=0>

    <tr>
      <td class="top">Copyright</td>
    </tr>
    <tr>
      <td class="content"><center>[DATTEBAYO] Content System ©2006 Anime's Tome</center>
</td>
    </tr>
    <tr>

      <td height="19" class="bottom">&nbsp;</td>
    </tr>
</table><br><br><a href='admin.php'>Back to Admin Area</a></center>
Here's the modified one:

Code: Select all

<?PHP require('incSession.php'); ?>
  <?PHP
  if ($sClearance[0]<2) {
echo "<META HTTP-EQUIV='Refresh'
      CONTENT='0; URL=admin.php?deny=true'>";
die;}
  ?>
  
<?
// set your infomation.
$dbhost='mysql233.secureserver.net';
$dbusername='dattebayo';
$dbuserpass='1010011';
$dbname='dattebayo';

// connect to the mysql database server.
mysql_connect ($dbhost, $dbusername, $dbuserpass);
mysql_select_db($dbname) or die("Cannot select database");

//Are they posting or just getting here?
if (isset($_POST["series"])) {
$series = $_POST["series"];
$table = $_POST["table"];

//Was a field left blank?
if($series==NULL|$table==NULL) {
echo "All fields must be filled in.";
}else{
//Has the series or table been used?
$checkname = mysql_query("SELECT series FROM index_test WHERE series='$series'");
$name_exist = mysql_num_rows($checkname);

$checkurl = mysql_query("SELECT table FROM index_test WHERE table='$table'");
$url_exist = mysql_num_rows($checkurl);

if ($series_exist>0|$table_exist>0) {
echo "The name or table of your anime is already in use";
}else{
//Everything seems good, lets insert.
$query = "INSERT INTO index_test (series, table) VALUES('$series','$table')";
mysql_query($query) or die(mysql_error());

$createquery = "CREATE TABLE `$table` (
  `id` int(11) NOT NULL auto_increment,
  `type` varchar(5) NOT NULL default '',
  `name` varchar(30) NOT NULL default '',
  `size` varchar(10) NOT NULL default '',
  `url` varchar(70) NOT NULL default '',
  PRIMARY KEY  (`id`)
) TYPE=MyISAM AUTO_INCREMENT=11 ;";

$create = mysql_query($createquery) or die(mysql_error());

if ($create)
    {
        echo "The anime series $name has been successfully created.";
    }
    else
    {
        echo "Series Creation Failed. Cancelling...";
        $delquery = "DELETE FROM index_test WHERE series='$series' LIMIT 1";
        mysql_query($delquery) or die (mysql_error());
    }
}
}
}
?>
<head>
<link rel="shortcut icon" href="../favicon.ico" />
<link rel="stylesheet" href="style_mini.css" type="text/css">
</head>
<h1>Category Manager - Add</h1>
<P>
<center>
<form action="addseries_test.php" method="POST">
    <table width=200 height="59" border=0 cellpadding=3 cellspacing=0>
    <tr>

      <td class="top">Series Name</td>
    </tr>
    <tr>
      <td class="content"><input type="text" size="20" maxlength="25" name="series">
</td>
    </tr>
    <tr>
      <td height="19" class="bottom">&nbsp;</td>
    </tr>
  </table>
  <p>
    <table width=200 height="59" border=0 cellpadding=3 cellspacing=0>
    <tr>

      <td class="top">Table Name</td>
    </tr>
    <tr>
      <td class="content"><input type="text" size="20" maxlength="10" name="table"></td>
    </tr>
    <tr>
      <td height="19" class="bottom">&nbsp;</td>
    </tr>
  </table>
  <p>
    <table width=200 height="59" border=0 cellpadding=3 cellspacing=0>
    <tr>

      <td class="top">Submit</td>
    </tr>
    <tr>
      <td class="content"><center><input type="submit" value="Submit"></center></td>
    </tr>
    <tr>
      <td height="19" class="bottom">&nbsp;</td>
    </tr>
  </table>
  </form>
</center>
<p><center>

  <table width=200 height="87" border=0 cellpadding=3 cellspacing=0>

    <tr>
      <td class="top">Copyright</td>
    </tr>
    <tr>
      <td class="content"><center>[DATTEBAYO] Content System is Copyright 2006 to Anime's Tome</center>
</td>
    </tr>
    <tr>

      <td height="19" class="bottom">&nbsp;</td>
    </tr>
</table><br><br><a href='admin.php'>Back to Admin Area</a><br><a href='series.php'>Back to Series Manager Menu</a></center>

Posted: Wed Nov 22, 2006 5:46 pm
by John Cartwright
mysql_connect ($dbhost, $dbusername, $dbuserpass) or die(mysql_error());

$checkname = mysql_query("SELECT name FROM naruto WHERE name='$name'") or die(mysql_error());
$name_exist = mysql_num_rows($checkname);

$checkurl = mysql_query("SELECT URL FROM naruto WHERE URL='$url'") or die(mysql_error());
$url_exist = mysql_num_rows($checkurl);

Notice I added some error checks, what does this output?

Posted: Wed Nov 22, 2006 6:11 pm
by Dirge of Cerberus

Code: Select all

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 'table FROM index_test WHERE table='tester'' at line 1

Posted: Wed Nov 22, 2006 6:12 pm
by feyd
Since "table" is a keyword, you probably need to place it in backticks: `

Posted: Thu Nov 23, 2006 4:09 pm
by Dirge of Cerberus
feyd wrote:Since "table" is a keyword, you probably need to place it in backticks: `
It just made it give me another error along with the old one. Reverted.

Posted: Thu Nov 23, 2006 4:12 pm
by John Cartwright
Dirge of Cerberus wrote:
feyd wrote:Since "table" is a keyword, you probably need to place it in backticks: `
It just made it give me another error along with the old one. Reverted.
What error?

Posted: Thu Nov 23, 2006 4:16 pm
by feyd
Did you use backticks?

` vs '
completely different characters.

Posted: Thu Nov 23, 2006 6:15 pm
by Dirge of Cerberus
Jcart wrote:
Dirge of Cerberus wrote:
feyd wrote:Since "table" is a keyword, you probably need to place it in backticks: `
It just made it give me another error along with the old one. Reverted.
What error?

Code: Select all

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/content/a/n/i/animestome/html/admin/addseries_test.php on line 34
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 'table) VALUES('dattebayo', 'tester')' at line 1
___________________________________________________
feyd wrote:Did you use backticks?

` vs '
completely different characters.
Yup. I used backticks.

Posted: Thu Nov 23, 2006 6:26 pm
by John Cartwright
from the error it looks like you havn't..

can you please re-post your code.

Posted: Thu Nov 23, 2006 6:26 pm
by volka
No, obviously you did not
Dirge of Cerberus wrote: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 'table) VALUES('dattebayo', 'tester')' at line 1

Posted: Thu Nov 23, 2006 6:39 pm
by Dirge of Cerberus

Code: Select all

<?PHP require('incSession.php'); ?>
  <?PHP
  if ($sClearance[0]<2) {
echo "<META HTTP-EQUIV='Refresh'
      CONTENT='0; URL=admin.php?deny=true'>";
die;}
  ?>
  
<?
// set your infomation.
$dbhost='mysql233.secureserver.net';
$dbusername='dattebayo';
$dbuserpass='1010011';
$dbname='dattebayo';

// connect to the mysql database server.
mysql_connect ($dbhost, $dbusername, $dbuserpass) or die(mysql_error());
mysql_select_db($dbname) or die("Cannot select database");

//Are they posting or just getting here?
if (isset($_POST["series"])) {
$series = $_POST["series"];
$table = $_POST["table"];

//Was a field left blank?
if($series==NULL|$table==NULL) {
echo "All fields must be filled in.";
}else{
//Has the series or table been used?
$checkname = mysql_query("SELECT series FROM index_test WHERE series=`$series`");
$name_exist = mysql_num_rows($checkname);

$checkurl = mysql_query("SELECT table FROM index_test WHERE table=`$table`");
$url_exist = mysql_num_rows($checkurl);

if ($series_exist>0|$table_exist>0) {
echo "The name or table of your anime is already in use";
}else{
//Everything seems good, lets insert.
$query = "INSERT INTO index_test (series, table) VALUES(`$series`, `$table`)";
mysql_query($query) or die(mysql_error());

$createquery = "CREATE TABLE `$table` (
  `id` int(11) NOT NULL auto_increment,
  `type` varchar(5) NOT NULL default '',
  `name` varchar(30) NOT NULL default '',
  `size` varchar(10) NOT NULL default '',
  `url` varchar(70) NOT NULL default '',
  PRIMARY KEY  (`id`)
) TYPE=MyISAM AUTO_INCREMENT=11 ;";

$create = mysql_query($createquery) or die(mysql_error());

if ($create)
    {
        echo "The anime series $name has been successfully created.";
    }
    else
    {
        echo "Series Creation Failed. Cancelling...";
        $delquery = "DELETE FROM index_test WHERE series='$series' LIMIT 1";
        mysql_query($delquery) or die (mysql_error());
    }
}
}
}
?>
<head>
<link rel="shortcut icon" href="../favicon.ico" />
<link rel="stylesheet" href="style_mini.css" type="text/css">
</head>
<h1>Category Manager - Add</h1>
<P>
<center>
<form action="addseries_test.php" method="POST">
    <table width=200 height="59" border=0 cellpadding=3 cellspacing=0>
    <tr>

      <td class="top">Series Name</td>
    </tr>
    <tr>
      <td class="content"><input type="text" size="20" maxlength="25" name="series">
</td>
    </tr>
    <tr>
      <td height="19" class="bottom">&nbsp;</td>
    </tr>
  </table>
  <p>
    <table width=200 height="59" border=0 cellpadding=3 cellspacing=0>
    <tr>

      <td class="top">Table Name</td>
    </tr>
    <tr>
      <td class="content"><input type="text" size="20" maxlength="10" name="table"></td>
    </tr>
    <tr>
      <td height="19" class="bottom">&nbsp;</td>
    </tr>
  </table>
  <p>
    <table width=200 height="59" border=0 cellpadding=3 cellspacing=0>
    <tr>

      <td class="top">Submit</td>
    </tr>
    <tr>
      <td class="content"><center><input type="submit" value="Submit"></center></td>
    </tr>
    <tr>
      <td height="19" class="bottom">&nbsp;</td>
    </tr>
  </table>
  </form>
</center>
<p><center>

  <table width=200 height="87" border=0 cellpadding=3 cellspacing=0>

    <tr>
      <td class="top">Copyright</td>
    </tr>
    <tr>
      <td class="content"><center>[DATTEBAYO] Content System is Copyright 2006 to Anime's Tome</center>
</td>
    </tr>
    <tr>

      <td height="19" class="bottom">&nbsp;</td>
    </tr>
</table><br><br><a href='admin.php'>Back to Admin Area</a><br><a href='series.php'>Back to Series Manager Menu</a></center>
I added the backticks to the mentioned part. The error hardly changed.

Code: Select all

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/content/a/n/i/animestome/html/admin/addseries_test.php on line 31

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/content/a/n/i/animestome/html/admin/addseries_test.php on line 34
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 'table) VALUES(`Dattebayo`, `tester`)' at line 1

Posted: Thu Nov 23, 2006 6:50 pm
by volka
$checkurl = mysql_query("SELECT table FROM index_test WHERE table=`$table`");
missing/wrong backticks
$query = "INSERT INTO index_test (series, table) VALUES(`$series`, `$table`)";
missing/wrong backticks
Do yourself a favor and rename tables and fields with the name table to something else.

Posted: Thu Nov 23, 2006 7:12 pm
by Dirge of Cerberus

Code: Select all

<?PHP require('incSession.php'); ?>
  <?PHP
  if ($sClearance[0]<2) {
echo "<META HTTP-EQUIV='Refresh'
      CONTENT='0; URL=admin.php?deny=true'>";
die;}
  ?>
  
<?
// set your infomation.
$dbhost='mysql233.secureserver.net';
$dbusername='dattebayo';
$dbuserpass='1010011';
$dbname='dattebayo';

// connect to the mysql database server.
mysql_connect ($dbhost, $dbusername, $dbuserpass) or die(mysql_error());
mysql_select_db($dbname) or die("Cannot select database");

//Are they posting or just getting here?
if (isset($_POST["series"])) {
$series = $_POST["series"];
$location = $_POST["location"];

//Was a field left blank?
if($series==NULL|$location==NULL) {
echo "All fields must be filled in.";
}else{
//Everything seems good, lets insert.
$query = "INSERT INTO index_test (series, name) VALUES(`$series`, `$location`)";
mysql_query($query) or die(mysql_error());

$createquery = "CREATE TABLE `$location` (
  `id` int(11) NOT NULL auto_increment,
  `type` varchar(5) NOT NULL default '',
  `name` varchar(30) NOT NULL default '',
  `size` varchar(10) NOT NULL default '',
  `url` varchar(70) NOT NULL default '',
  PRIMARY KEY  (`id`)
) TYPE=MyISAM AUTO_INCREMENT=11 ;";

$create = mysql_query($createquery) or die(mysql_error());

if ($create)
    {
        echo "The anime series $name has been successfully created.";
    }
    else
    {
        echo "Series Creation Failed. Cancelling...";
        $delquery = "DELETE FROM index_test WHERE series='$series' LIMIT 1";
        mysql_query($delquery) or die (mysql_error());
    }
}
}
?>
<head>
<link rel="shortcut icon" href="../favicon.ico" />
<link rel="stylesheet" href="style_mini.css" type="text/css">
</head>
<h1>Category Manager - Add</h1>
<P>
<center>
<form action="addseries_test.php" method="POST">
    <table width=200 height="59" border=0 cellpadding=3 cellspacing=0>
    <tr>

      <td class="top">Series Name</td>
    </tr>
    <tr>
      <td class="content"><input type="text" size="20" maxlength="25" name="series">
</td>
    </tr>
    <tr>
      <td height="19" class="bottom">&nbsp;</td>
    </tr>
  </table>
  <p>
    <table width=200 height="59" border=0 cellpadding=3 cellspacing=0>
    <tr>

      <td class="top">Table Name</td>
    </tr>
    <tr>
      <td class="content"><input type="text" size="20" maxlength="10" name="location"></td>
    </tr>
    <tr>
      <td height="19" class="bottom">&nbsp;</td>
    </tr>
  </table>
  <p>
    <table width=200 height="59" border=0 cellpadding=3 cellspacing=0>
    <tr>

      <td class="top">Submit</td>
    </tr>
    <tr>
      <td class="content"><center><input type="submit" value="Submit"></center></td>
    </tr>
    <tr>
      <td height="19" class="bottom">&nbsp;</td>
    </tr>
  </table>
  </form>
</center>
<p><center>

  <table width=200 height="87" border=0 cellpadding=3 cellspacing=0>

    <tr>
      <td class="top">Copyright</td>
    </tr>
    <tr>
      <td class="content"><center>[DATTEBAYO] Content System is Copyright 2006 to Anime's Tome</center>
</td>
    </tr>
    <tr>

      <td height="19" class="bottom">&nbsp;</td>
    </tr>
</table><br><br><a href='admin.php'>Back to Admin Area</a><br><a href='series.php'>Back to Series Manager Menu</a></center>

I changed the code slightly, but now it gives me THIS error:

Code: Select all

Unknown column 'name' in 'field list'

Posted: Thu Nov 23, 2006 7:48 pm
by volka
And exactly what query is causing that error?
In case of doubt you might want to add some output to each or die(), e.g. the sql query itself.