php can,t update

Discussion in 'PHP' started by ducca, Oct 25, 2006.

  1. #1
    Hi all,
    I,m playing with some simple PHP code to help me learn the basics of php and mysql
    My problem is the code won,t update and insert into the database,
    I pass the id from one page to another which works fine.I can delete a record but not update, Can anyone help
    heres the code,
    I,m sure its something simple but I just can,t see it
    Thanks in advance
    
    <?php
    
    include("contentdb.php");
    $id=$_GET['id'];
    $question=$_POST['question'];
    $opt1=$_POST['opt1'];
    $opt2=$_POST['opt2'];
    $opt3=$_POST['opt3'];
    
    
    if($update)
    {
    $result = mysql_query("UPDATE $table SET question='$question',opt1='$opt1',opt2='$opt2',opt3='$opt3',answer='$answer' WHERE id'$id'",$db);
    
    echo "<br><br>The quiz has been succesfully updated.<br><br>\n";
    }
    else 
    {
    $result = mysql_query("SELECT * FROM $table WHERE id='$id'",$db);
    $myrow = mysql_fetch_array($result);
    }
    ?>
    
    
    <form method="post" action= "quizinfo.php">
    <input type="hidden" name="id" value="<?php echo $myrow["id"]?>">
        <b>Question:</b><br>
        <input type="text" name="question" value="<?php echo $myrow["question"]?>" size="50">
        <br>
        <b>Option 1:</b><br>
        <input type="Text" name="opt1" value="<?php echo $myrow["opt1"]?>" size="30">
        <br>
        <b>Option 2:</b><br>
        <input type="Text" name="opt2" value="<?php echo $myrow["opt2"]?>" size="30">
        <br>
        <b>Option 3:</b><br>
        <input type="Text" name="opt3" value="<?php echo $myrow["opt3"]?>" size="30">
        <br>
        <b>Answer</b> (must be identical to correct option):<br>
        <input type="Text" name="answer" value="<?php echo $myrow["answer"]?>" size="30">
    	
        <br>
        <br>
    	
    <input type="Submit" name="update" value="Update information"></form>
    <?php
    	  mysql_close();
    	  ?>
    PHP:
    Thanks again
     
    ducca, Oct 25, 2006 IP
  2. T0PS3O

    T0PS3O Feel Good PLC

    Messages:
    13,219
    Likes Received:
    777
    Best Answers:
    0
    Trophy Points:
    0
    #2
    T0PS3O, Oct 25, 2006 IP
  3. ducca

    ducca Peon

    Messages:
    20
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #3
    Thanks for reply,
    I,m not getting any errors even with mysql_error()) in the code,
    regarding $update I thought the variable was created when the update button is called
    thanks again
     
    ducca, Oct 25, 2006 IP
  4. php-lover

    php-lover Active Member

    Messages:
    261
    Likes Received:
    21
    Best Answers:
    0
    Trophy Points:
    58
    #4
    if($_POST['update']){
    $result = mysql_query "UPDATE $table SET question='$question',opt1='$opt1',opt2='$opt2',opt3='$opt3',answer='$answer' WHERE id='$id' ",$db);
    echo "<br><br>The quiz has been succesfully updated.<br><br>\n";
    }else {
    $result = mysql_query("SELECT * FROM $table WHERE id='$id',$db);
    $myrow = mysql_fetch_array($result);
    }
    ?>

    I don't see an $table above and where you get your id from?
     
    php-lover, Oct 25, 2006 IP
  5. ducca

    ducca Peon

    Messages:
    20
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #5
    Thanks for replies,
    tried both solutions, still no joy
    driving me mad!!!!!!!!!
     
    ducca, Oct 25, 2006 IP
  6. php-lover

    php-lover Active Member

    Messages:
    261
    Likes Received:
    21
    Best Answers:
    0
    Trophy Points:
    58
    #6
    $id = $_POST['id'];
     
    php-lover, Oct 25, 2006 IP
  7. ducca

    ducca Peon

    Messages:
    20
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #7
    $table is in
    include("contentdb.php"); or if I put the table name in, still no result or error message.
    and I pass the ID from here same place I pass id for the delete and that works fine
    thanks again
    cheers
    <?php

    include("contentdb.php");
    $id = $_GET["id"];
    if($_GET["cmd"]=="delete")
    {
    $sql = mysql_query("DELETE FROM $table WHERE id='$id'",$db);
    //$result = mysql_query($sql);
    //echo "Row deleted!";

    }

    ?>
    <?php

    include("contentdb.php");

    $result = mysql_query("SELECT id, question FROM $table ORDER BY id",$db);

    echo "<table>";

    while ($row = mysql_fetch_array($result))
    {


    $id = $row["id"];
    $question = $row["question"];


    if ($alternate == "1") {
    $color = "#ffffff";
    $alternate = "2";
    }
    else {
    $color = "#efefef";
    $alternate = "1";
    }
    //print ("<TABLE BORDER=1 WIDTH=\"45%\" CELLSPACING=1 CELLPADDING=2 ALIGN=CENTER>\n");
    echo "<tr bgcolor=$color>
    <td>$id:</td><td>$question</td>

    //Id passed here!!

    <td>[ <a href='updatequiz.php?id=$id'>Update</a> ]</td>

    //Id passed here!!
    <td>[<a href='deletequiz2.php?cmd=delete&id=$id'onClick=\"return confirm('Are you sure?')\">delete</a>]</td></tr>";
    }
    echo "</table>";
    mysql_close();
    ?>
     
    ducca, Oct 25, 2006 IP
  8. T0PS3O

    T0PS3O Feel Good PLC

    Messages:
    13,219
    Likes Received:
    777
    Best Answers:
    0
    Trophy Points:
    0
    #8
    First things first. Do some basic debugging. Just echo something in your if statement on both sides so you know which side evaluates to true. Then you move to the next bit.

    And please post using [php][/php] tags.
     
    T0PS3O, Oct 25, 2006 IP
  9. aaron_nimocks

    aaron_nimocks Im kind of a big deal Staff

    Messages:
    5,563
    Likes Received:
    627
    Best Answers:
    0
    Trophy Points:
    420
    #9
    On line 9 do

    echo $update;
    PHP:
    and see what you get. I dont think its ever going to be true.
     
    aaron_nimocks, Oct 25, 2006 IP
  10. ducca

    ducca Peon

    Messages:
    20
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #10
    Thanks for replies all
    Apologies for not using tags,

    after your advice I get this error

    Could not update postYou have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''9'' at line 1

    Thanks again
     
    ducca, Oct 25, 2006 IP
  11. php-lover

    php-lover Active Member

    Messages:
    261
    Likes Received:
    21
    Best Answers:
    0
    Trophy Points:
    58
    #11
    $result = mysql_query "UPDATE $table SET question='$question',opt1='$opt1',opt2='$opt2',opt3='$opt3',answer='$answer' WHERE id='$id' ",$db);
     
    php-lover, Oct 25, 2006 IP
  12. andysyme

    andysyme Member

    Messages:
    91
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    43
    #12
    After following advice, what's your code look like now?

    Andy
     
    andysyme, Oct 25, 2006 IP
  13. ducca

    ducca Peon

    Messages:
    20
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #13
    The code now updates but inputs only blank entries into the database but doesn,t pass the variable id,the code updates Immediately when I select the update link,
    heres where I select update
    
    <?php
    
    include("contentdb.php");
    $id = $_GET["id"];
    if($_GET["cmd"]=="delete")
    {
        $sql = mysql_query("DELETE  FROM $table WHERE id='$id'",$db);
        //$result = mysql_query($sql);
        //echo "Row deleted!";
    	
    }
    
    ?>
    <?php
    
    include("contentdb.php");
    
    $result = mysql_query("SELECT id, question FROM $table ORDER BY id",$db);
    
    echo "<table>";
    
    while ($row = mysql_fetch_array($result)) 
    {
    	
    	
    	$id = $row["id"]; 
    	$question = $row["question"];
    	
    	
    	if ($alternate == "1") { 
    	$color = "#ffffff"; 
    	$alternate = "2"; 
    	} 
    	else { 
    	$color = "#efefef"; 
    	$alternate = "1"; 
    	} 
    	//print ("<TABLE BORDER=1 WIDTH=\"45%\" CELLSPACING=1 CELLPADDING=2 ALIGN=CENTER>\n");
    	echo "<tr bgcolor=$color>
    	<td>$id:</td><td>$question</td>
    
    
    /////////////////////////Link to pass id////////////////////
    	<td>[ <a href='updatequiz.php?id=$id'>Update</a> ]</td>
    
    ////////////////////////////////////////////////////////////////////////////
    	<td>[<a href='deletequiz2.php?cmd=delete&id=$id'onClick=\"return confirm('Are you sure?')\">delete</a>]</td></tr>"; 
    } 
    echo "</table>";
      mysql_close();
    ?>
    
    PHP:


    And heres were I'm trying to update


    
    <?php
    
    include("contentdb.php");
    $id=$_GET['id'];
    $question=$_POST['question'];
    $opt1=$_POST['opt1'];
    $opt2=$_POST['opt2'];
    $opt3=$_POST['opt3'];
    //$update = $_POST["update"];
    
    //echo $update; 
    
    if($update)
    {
    $result = mysql_query("SELECT * FROM $table WHERE id='$id'",$db);
    $myrow = mysql_fetch_array($result);
    
    }
    else
    {
    $result = mysql_query("UPDATE $table SET question='$question',opt1='$opt1',opt2='$opt2',opt3='$opt3',answer='$answer' WHERE id='$id'",$db)
    or die ("Could not update post". mysql_error());
    echo "<br><br>The quiz has been succesfully updated.<br><br>\n";
    }
    ?>
    
    <!--remember elseif ($ID)-->
    
    <form method="post" action= "quizinfo.php">
    <input type="hidden" name="id" value="<?php echo $myrow["id"]?>">
        <b>Question:</b><br>
        <input type="text" name="question" value="<?php echo $myrow["question"]?>" size="50">
        <br>
        <b>Option 1:</b><br>
        <input type="Text" name="opt1" value="<?php echo $myrow["opt1"]?>" size="30">
        <br>
        <b>Option 2:</b><br>
        <input type="Text" name="opt2" value="<?php echo $myrow["opt2"]?>" size="30">
        <br>
        <b>Option 3:</b><br>
        <input type="Text" name="opt3" value="<?php echo $myrow["opt3"]?>" size="30">
        <br>
        <b>Answer</b> (must be identical to correct option):<br>
        <input type="Text" name="answer" value="<?php echo $myrow["answer"]?>" size="30">
    	
        <br>
        <br>
    	
    <input type="Submit" name="update" value="Update information"></form>
    <?php
    	  mysql_close($db);
    	  ?>
    
    PHP:
    I'm trying to pass the id so the record from the id displays in the form so I can then update the data.
    Thanks for any help
     
    ducca, Oct 25, 2006 IP
  14. T0PS3O

    T0PS3O Feel Good PLC

    Messages:
    13,219
    Likes Received:
    777
    Best Answers:
    0
    Trophy Points:
    0
    #14
    Change

    $result = mysql_query("SELECT * FROM $table WHERE id='$id'",$db);
    PHP:
    to

    
    $raw_query = "SELECT * FROM $table WHERE id='$id'";
    echo $raw_query;
    $result = mysql_query($raw_query,$db);
    PHP:
    Then inspect what the query actually looks like to MySQL.

    Listen, when debugging YOU HAVE TO ECHO STUFF everywhere! Echo everything so you know what is going on. If you don't echo the lot, you don't know what is going on and you can only guess. Start ECHOING!

    I've been coding for years so take that advice, it will speed things up and you probably could have fixed this in seconds. Once you start echoing everything you will see where the blank stuff is. Then you move back up and echo something there. etc. etc.

    Also, your code is VERY INSECURE. Whatever tutorial or book you are using, it's evil. You are taking a $_GET and putting it straight into an SQL query. Give me the URL and I destroy your site in seconds. Read about SQL injection and mysql_real_escape() etc.

    Just in case I hadn't made myself clear, get in the habit of debugging properly. Echoing is my favourite.
     
    T0PS3O, Oct 25, 2006 IP
  15. andysyme

    andysyme Member

    Messages:
    91
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    43
    #15
    still don;t get the if($update);

    what does echoing that show?

    Andy
     
    andysyme, Oct 25, 2006 IP