Jump to content

PHP/SQL Error - Code Posted


C4talyst

Recommended Posts

Hello, I'm trying to create a very simple page that will allow my users to edit six fields located in a mysql table.  I'm kinda new to php/mysql and am using borrowed code from a tutorial website.  The page displays the table contents ok, but when I make changes and hit submit I get this error:

 

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/virtual/site20/fst/var/www/html2/edit.php on line 59

Query failed: 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 '' at line 1 Actual query:

 

Here is the code w/ line 59 bolded:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
    "http://www.w3.org/TR/html4/loose.dtd">


<html>

<head>

    <title>ACDC Membership Database - Add Nation</title>
    <meta http-equiv="Content-type" content="text/html; charset=iso-8859-1">
    <meta http-equiv="Content-Language" content="en-us">
    <meta name="Robots" content="index,follow">
    <style type="text/css">@import "style.css";</style>

</head>

<body>

<div id="mdb_top">
<div id="mdb_nav" style="padding: 2px;" align="center"><a href="index.php">MDB Home</a> | <a href="add.php">Add Nation</a> | <a href="edit.php">Edit Nation</a> | <a href="search.php">Find Nation</a></div>
</div>

<? 
//connect to mysql
//change user and password to your mySQL name and password
mysql_connect("localhost","username","password"); 

//select which database you want to edit
mysql_select_db("database");

//If cmd has not been initialized
if(!isset($cmd)) 
{
   //display all the news
   $result = mysql_query("select * from members order by id"); 
   
   //run the while loop that grabs all the news scripts
   while($r=mysql_fetch_array($result)) 
   { 
      //grab the title and the ID of the news
      $id=$r["id"];
      $cnnation=$r["cnnation"];
      $cnruler=$r["cnruler"];
      $acdcuser=$r["acdcuser"];
      $positions=$r["positions"];
      $nationlink=$r["nationlink"];
    }
}
?>

<?
if($_GET["cmd"]=="edit" || $_POST["cmd"]=="edit")
{
   if (!isset($_POST["submit"]))
   {
      $id = $_GET["id"];
      $sql = "SELECT * FROM members WHERE id=$id";
      $result = mysql_query($sql);        
      [b]$myrow = mysql_fetch_array($result)[/b]
      or die ("Query failed: " . mysql_error() . " Actual query: " . $result);
      echo ">$result<";
      ?>

      <table border="0" cellpadding="3" cellspacing="3" width="700">
      <form action="edit.php" method="post">
      <input type=hidden name="id" value="<?php echo $myrow["id"] ?>">
       <tr>
        <td width="700" colspan="6" bgcolor="#414B3F">
        <h3>Edit User in ACDC Membership Database</h3>
        </td>
       </tr>
       <tr>
      	<td width="100" bgcolor="#768872"  valign="top" align="left">
      	 <b>ID</b>
      	</td>
      	<td width="130" bgcolor="#768872" valign="top" align="left">
      	 <b>Nation</b>
      	</td>
      	<td width="130" bgcolor="#768872" valign="top" align="left">
      	 <b>Ruler</b>
      	</td>
      	<td width="120" bgcolor="#768872" valign="top" align="left">
      	 <b>ACDC User</b>
      	</td>
      	<td width="120" bgcolor="#768872" valign="top" align="left">
      	 <b>Nation Link</b>
      	</td>
      	<td width="100" bgcolor="#768872" valign="top" align="left">
      	 <b>Positions</b>
      	</td>
      </tr>
      <tr>
       <td width="100" bgcolor="#9DAB9B"  valign="top" align="left">
       
       </td>
       <td width="130" bgcolor="#9DAB9B"  valign="top" align="left">
       <input class="form" type="text" name="cnnation" value="<?php echo $myrow["cnnation"] ?>" size="11" />
       </td>
       <td width="130" bgcolor="#9DAB9B" valign="top" align="left">
       <input class="form" type="text" name="cnruler" value="<?php echo $myrow["cnruler"] ?>" size="11" />
       </td>
       <td width="120" bgcolor="#9DAB9B" valign="top" align="left">
       <input class="form" type="text" name="acdcuser" value="<?php echo $myrow["acdcuser"] ?>" size="11" />
       </td>
       <td width="120" bgcolor="#9DAB9B" valign="top" align="left">
       <input class="form" type="text" name="nationlink" value="<?php echo $myrow["nationlink"] ?>" size="11" />
       </td>
       <td width="120" bgcolor="#9DAB9B" valign="top" align="left">
       <select class="form" type="text" name="positions" value="<?php echo $myrow["positions"] ?>">
       <option value ="Triumvirate">Triumvirate</option>
       <option value ="Senator">Senator</option>
       <option value ="MoD">MoD</option>
       <option value ="MoC">MoC</option>
       <option value ="MoE">MoC</option>
       <option value ="MoFA">MoFA</option>
       <option value ="MoIA">MoIA</option>
       </select>
       </td>
      </tr>
      <tr>
       <td width="700" bgcolor="#768872" align="center" colspan="6">
        <input class="form" type="submit" value="SUBMIT" />
        <input type="hidden" name="cmd" value="edit">
       </td>
      </tr>
      </form>
     </table>
   
   <? } ?>

<?
   if ($_POST["$submit"])
   {
      $id = $_POST["id"];
      $title = $_POST["cnnation"];
      $title = $_POST["cnruler"];
      $title = $_POST["acdcuser"];
      $title = $_POST["positions"];
      $title = $_POST["nationlink"];

      $sql = "UPDATE members SET id='$id',cnnation='$cnnation',cnruler='$cnruler',acdcuser='$acdcuser',positions='$positions',nationlink='$nationlink' WHERE id=$id";

      $result = mysql_query($sql);
      echo "Thank you! Information updated.";
   }
}
?>

</body>

Link to comment
https://forums.phpfreaks.com/topic/70397-phpsql-error-code-posted/
Share on other sites

To debug your mysql/php code, after any line that has a mysql function in it, make it say or die(mysql_error());

 

So change your code to this: (PS: did you update the mysql_connect line to have your login info?)

<?php
mysql_connect("localhost","username","password") or die(mysql_error());
?>

 

ETC. All of the mysql lines, add that error bit. Okay?

 

Also, check into this function: http://php.net/mysql_num_rows

 

So you don't have a while loop that won't work.

So for instance, change these lines:

<?php
$sql = "UPDATE members SET id='$id', cnnation='$cnnation', cnruler='$cnruler', acdcuser='$acdcuser', positions='$positions', nationlink='$nationlink' WHERE id=$id";
$result = mysql_query($sql);
?>

To this:

<?php
$sql = "UPDATE members SET id='$id', cnnation='$cnnation', cnruler='$cnruler', acdcuser='$acdcuser', positions='$positions', nationlink='$nationlink' WHERE id=$id";
$result = mysql_query($sql) or die(mysql_error().' SQL: '.$sql);
?>

Thanks for explaining how to debug my code.  I added the die mesg after each function and this is what gets displayed after I hit the submit button in hopes of updating the database:

 

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 '' at line 1

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.