php code does not edit msql data correctly

  • conundrum
  • Born
  • Born
  • conundrum
  • Posts: 3

Post 3+ Months Ago

Hi everyone!

I've written a script that displays rows of data in the input fields from mysql database.

I have a tick box next to each row so that i can tick it to select a row and either press "Delete" button, which works fine, or edit button to change the data when i change something in any of a row's field(s). The problem is that it would only change the data in a row when there is only one row of data in the database. When there is more than one row, it would only work with the bottom row(1st entered row) and if i try to edit other rows, the edited row would copy all the data from another row, so then there will be to duplicate rows. I've posted the code below.

CODE:
Code: [ Select ]
<?php
    $connection = mysql_connect("localhost", "root", "");
    
    mysql_select_db("whitg22");

    function deletenames()
    {
        $result = mysql_query("SELECT * FROM hits");
        
        if (($row = mysql_fetch_array($result)) != 0)
        {
            echo "<table border=0>";

            echo "<tr bgcolor=#cccccc>
                <td align=center>Title</td>
                <td align=center>Artist</td>
                <td align=center>Year</td>
                <td align=center>Genreid</td>
                <td align=center>Chart Position</td>
                <td align=center>Type</td>
                <td align=center>Price</td>
                <td align=center>Quantity</td>
                <td align=center>Choose a Hit</td>
                </tr>";
            do
            {
                $title = $row['title'];
                $artist = $row['artist'];
                $year = $row['year'];
                $genreid = $row['genreid'];
                $chartposition = $row['chartposition'];
                $type = $row['type'];
                $price = $row['price'];
                $quantity = $row['quantity'];
                $id = $title;    
                
                echo "<form method='post' action='change.php'>
                    <tr>
                    <td align='center'>
                    <input type='text' name='title' value='" . $title . "' size='30' maxlength='30'></td>
                    <td align='center'>
                    <input type='text' name='artist' value='" . $artist . "'></td>
                    <td align='center'>
                    <input type='text' name='year' value='" . $year . "' size='4' maxlength='4'></td>
                    <td align='center'>
                    <input type='text' name='genreid' value='" . $genreid . "' size='5' maxlength='5'></td>
                    <td align='center'>
                    <input type='text' name='chartposition' value='" . $chartposition . "' size='10' maxlength='10'></td>
                    <td align='center'>
                    <input type='text' name='type' value='" . $type . "' size='5' maxlength='5'></td>
                    <td align='center'>
                    <input type='text' name='price' value='" . $price . "' size='4' maxlength='4'></td>
                    <td align='center'>
                    <input type='text' name='quantity' value='" . $quantity . "' size='5' maxlength='5'></td>
                    <td align='center'>
                    <input type='checkbox' name='id' value='" . $title . "'></td></tr>";
             }

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

            echo "<tr><td><input type='submit' name='delete' value='Delete'>";
            
            echo "<input type='submit' name='edit' value='Edit'></td></tr></table></form>";            
        }

        else
        {    
        echo "No records found!";
        }    
    }

    deletenames();

    if(isset($delete))
    {
        mysql_query("DELETE FROM hits WHERE title = '$id'");
    }

    if(isset($edit))            
    {
        mysql_query("UPDATE hits SET title = '$title', artist = '$artist', year = '$year', genreid = '$genreid',
                        chartposition = '$chartposition', type = '$type', price = '$price', quantity = '$quantity'
                   WHERE title = '$id'");
    }
?>
  1. <?php
  2.     $connection = mysql_connect("localhost", "root", "");
  3.     
  4.     mysql_select_db("whitg22");
  5.     function deletenames()
  6.     {
  7.         $result = mysql_query("SELECT * FROM hits");
  8.         
  9.         if (($row = mysql_fetch_array($result)) != 0)
  10.         {
  11.             echo "<table border=0>";
  12.             echo "<tr bgcolor=#cccccc>
  13.                 <td align=center>Title</td>
  14.                 <td align=center>Artist</td>
  15.                 <td align=center>Year</td>
  16.                 <td align=center>Genreid</td>
  17.                 <td align=center>Chart Position</td>
  18.                 <td align=center>Type</td>
  19.                 <td align=center>Price</td>
  20.                 <td align=center>Quantity</td>
  21.                 <td align=center>Choose a Hit</td>
  22.                 </tr>";
  23.             do
  24.             {
  25.                 $title = $row['title'];
  26.                 $artist = $row['artist'];
  27.                 $year = $row['year'];
  28.                 $genreid = $row['genreid'];
  29.                 $chartposition = $row['chartposition'];
  30.                 $type = $row['type'];
  31.                 $price = $row['price'];
  32.                 $quantity = $row['quantity'];
  33.                 $id = $title;    
  34.                 
  35.                 echo "<form method='post' action='change.php'>
  36.                     <tr>
  37.                     <td align='center'>
  38.                     <input type='text' name='title' value='" . $title . "' size='30' maxlength='30'></td>
  39.                     <td align='center'>
  40.                     <input type='text' name='artist' value='" . $artist . "'></td>
  41.                     <td align='center'>
  42.                     <input type='text' name='year' value='" . $year . "' size='4' maxlength='4'></td>
  43.                     <td align='center'>
  44.                     <input type='text' name='genreid' value='" . $genreid . "' size='5' maxlength='5'></td>
  45.                     <td align='center'>
  46.                     <input type='text' name='chartposition' value='" . $chartposition . "' size='10' maxlength='10'></td>
  47.                     <td align='center'>
  48.                     <input type='text' name='type' value='" . $type . "' size='5' maxlength='5'></td>
  49.                     <td align='center'>
  50.                     <input type='text' name='price' value='" . $price . "' size='4' maxlength='4'></td>
  51.                     <td align='center'>
  52.                     <input type='text' name='quantity' value='" . $quantity . "' size='5' maxlength='5'></td>
  53.                     <td align='center'>
  54.                     <input type='checkbox' name='id' value='" . $title . "'></td></tr>";
  55.              }
  56.             while ($row = mysql_fetch_array($result));
  57.             echo "<tr><td><input type='submit' name='delete' value='Delete'>";
  58.             
  59.             echo "<input type='submit' name='edit' value='Edit'></td></tr></table></form>";            
  60.         }
  61.         else
  62.         {    
  63.         echo "No records found!";
  64.         }    
  65.     }
  66.     deletenames();
  67.     if(isset($delete))
  68.     {
  69.         mysql_query("DELETE FROM hits WHERE title = '$id'");
  70.     }
  71.     if(isset($edit))            
  72.     {
  73.         mysql_query("UPDATE hits SET title = '$title', artist = '$artist', year = '$year', genreid = '$genreid',
  74.                         chartposition = '$chartposition', type = '$type', price = '$price', quantity = '$quantity'
  75.                    WHERE title = '$id'");
  76.     }
  77. ?>
  • _Leo_
  • Proficient
  • Proficient
  • User avatar
  • Posts: 279
  • Loc: Buenos Aires, Argentina

Post 3+ Months Ago

Is your "title" field primary key in the database?
Are you sure each row has a different value in title column?

Not sure what's goin on. But you should use a int() field as ID, and this field should be primary key of the table.
  • conundrum
  • Born
  • Born
  • conundrum
  • Posts: 3

Post 3+ Months Ago

Thanks for replying.
I'm not sure what is a primary key, but each row has a different value in the title column.
  • Axe
  • Genius
  • Genius
  • User avatar
  • Posts: 5739
  • Loc: Sub-level 28

Post 3+ Months Ago

Could you please learn to use the [code] tag to encase code. That way it displays the code, and doesn't try to render it in the page.

Thank you.
  • conundrum
  • Born
  • Born
  • conundrum
  • Posts: 3

Post 3+ Months Ago

I undrestand what the primary key is. Yes, the "title" field is the primary key in the table. I do have some int() type fields, but they can not uniquely identify each row.
  • _Leo_
  • Proficient
  • Proficient
  • User avatar
  • Posts: 279
  • Loc: Buenos Aires, Argentina

Post 3+ Months Ago

Ok, you need a primary key. This property of column should be marked in the DB so it won't make two rows having the same value. Then, you can use that column to uniquely identify a row.

Now, I will take a look to your code.
  • _Leo_
  • Proficient
  • Proficient
  • User avatar
  • Posts: 279
  • Loc: Buenos Aires, Argentina

Post 3+ Months Ago

Ok, you have an HTML problem there. You are making an HTML file with as much forms as rows you have. Each FORM sends its own fields only. Then, when you click two rows, only ONE value for "title" field is sent.

In your code, you are using a SQL query which is able to delete ONE row only.
Code: [ Select ]
mysql_query("DELETE FROM hits WHERE title = '$id'");

Will only delete ONE row, the one whos title field is equal to $id

In order to do it work, you have:

1. to build up a "BIG" HTML form using arrays to get values. Let's say ...name="id[]"...
2. to use the array $id[] to get all marked ID using checkboxes or some multiple selection method.

Post Information

  • Total Posts in this topic: 7 posts
  • Users browsing this forum: No registered users and 94 guests
  • You cannot post new topics in this forum
  • You cannot reply to topics in this forum
  • You cannot edit your posts in this forum
  • You cannot delete your posts in this forum
  • You cannot post attachments in this forum
 
 

© 1998-2014. Ozzu® is a registered trademark of Unmelted, LLC.