MySQL Error.

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
Dirge of Cerberus
Forum Commoner
Posts: 38
Joined: Mon Nov 20, 2006 9:01 pm

MySQL Error.

Post 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>
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post 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?
Dirge of Cerberus
Forum Commoner
Posts: 38
Joined: Mon Nov 20, 2006 9:01 pm

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Since "table" is a keyword, you probably need to place it in backticks: `
Dirge of Cerberus
Forum Commoner
Posts: 38
Joined: Mon Nov 20, 2006 9:01 pm

Post 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.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post 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?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Did you use backticks?

` vs '
completely different characters.
Dirge of Cerberus
Forum Commoner
Posts: 38
Joined: Mon Nov 20, 2006 9:01 pm

Post 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.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

from the error it looks like you havn't..

can you please re-post your code.
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post 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
Dirge of Cerberus
Forum Commoner
Posts: 38
Joined: Mon Nov 20, 2006 9:01 pm

Post 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
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post 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.
Dirge of Cerberus
Forum Commoner
Posts: 38
Joined: Mon Nov 20, 2006 9:01 pm

Post 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'
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post 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.
Post Reply