PHP/SQL Error - Code Posted

C4talyst · September 23, 2007

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>

C4talyst · September 23, 2007

Duhh, I shoulda known the code box would not parse my bold tags...here is the problematic line:

$myrow = mysql_fetch_array($result)

Any help figuring this out is greatly appreciated...I've been banging my head all day over this...pathetic, I know!

rarebit · September 23, 2007

Try your query like this to get error:

$result = mysql_query($s);
if (!$result) {
    die('Invalid query: ' . mysql_error());
}

I tend to put quotes round my data (but nums usually ok), e.g. id = '$id'...

teng84 · September 23, 2007

what is the data type of your id

also try adding a thick

$sql = "SELECT * FROM members WHERE `id` = $id";

C4talyst · September 23, 2007

The mysql field is int(, you guys might as well be talking chinese to me right now...lol.

Jessica · September 23, 2007

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.

C4talyst · September 23, 2007

Thanks for the info, in my page the database connection info is correct as I'm pulling the data, I just can't upload. I will try to add your mysql_error code to the page and see if I can produce any other error messages.

Jessica · September 23, 2007

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);
?>

C4talyst · September 23, 2007

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

Jessica · September 23, 2007

Make sure you print out the SQL, you will be able to see the errors then.

Also, I think you are not escaping your data.

Check out mysql_real_escape_string and google SQL injection.

teng84 · September 23, 2007

this is not good but try

echo your query and copy the result then directly run it in your db

Sign In

PHP/SQL Error - Code Posted

Recommended Posts

C4talyst

Link to comment

Share on other sites

C4talyst

Link to comment

Share on other sites

rarebit

Link to comment

Share on other sites

teng84

Link to comment

Share on other sites

C4talyst

Link to comment

Share on other sites

Jessica

Link to comment

Share on other sites

C4talyst

Link to comment

Share on other sites

Jessica

Link to comment

Share on other sites

C4talyst

Link to comment

Share on other sites

Jessica

Link to comment

Share on other sites

teng84

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information