MySQL insert row from HTML table into db table

Question

I am using a for loop to construct a HTML table from the contents of a MySQL table select query. I have a link on the end of each row to copy that row into another table.

I'm unsure how to get the data from the table row for the MySQL insert query - I have marked the place where I'm struggling with XXX.

<?php

mysql_select_db("cardatabase");

$link = mysql_connect("localhost", "root", "password");
$query = "SELECT * from cars";
$result = mysql_query($query);

if($_GET['rent']) {
    $rent = "INSERT INTO rentedcars VALUES('XXX','XXX','XXX','XXX','XXX','XXX','XXX','XXX','XXX','XXX')";
    mysql_query($rent);
    echo "<meta http-equiv='refresh' content='0;url=rent.php'/>";
}

echo "<table>";
echo "<tr><td>ID</td><td>Make</td><td>Model</td><td>Fuel Type</td><td>Transmission</td><td>Engine Size</td><td>Doors</td><td>Amount</td><td>Available</td><td>Date Added</td><td>Remove</td></tr>";

for ($i = 0; $i < mysql_num_rows($result); $i++) {
    $row = mysql_fetch_object($result);
    echo "<tr>
        <td>$row->ID</td>
        <td>$row->CARMAKE</td>
        <td>$row->CARMODEL</td>
        <td>$row->FUELTYPE</td>
        <td>$row->TRANSMISSION</td>
        <td>$row->ENGINESIZE</td>
        <td>$row->DOORS</td>
        <td>$row->AMOUNT</td>
        <td>$row->AVAILABLE</td>
        <td>$row->DATEADDED</td>
        <td><a href='?rent=$row->ID'>Rent</a></td>
        </tr>";
}
echo "</table>";

edit (updated code):

<?php

mysql_select_db ("cardatabase");

$link   = mysql_connect ("localhost", "root", "password");
$query  = "SELECT * from cars";
$result = mysql_query ($query);

if($_GET['rent']) {
    $query_car = sprintf("SELECT * from cars WHERE ID=%s",$_GET['rent']);
    $rslt      = mysql_query($query_car);
    $car       = mysql_fetch_object ($rslt);
    $rent      = "INSERT INTO rentedcars VALUES('$car->ID','$car->CARMAKE','$car->CARMODEL','$car->FUELTYPE','$car->TRANSMISSION','$car->ENGINESIZE','$car->DOORS','$car->AMOUNT','$car->AVAILABLE','$car->DATEADDED')";
    mysql_query($rent);
    echo "<meta http-equiv='refresh' content='0;url=rent.php'/>";
}

echo "<table>";
echo "<tr>";
echo "<td>ID</td><td>Make</td><td>Model</td><td>Fuel Type</td><td>Transmission</td><td>Engine Size</td><td>Doors</td><td>Amount</td><td>Available</td><td>Date Added</td><td>Remove</td>";
echo "</tr>";

while ($row = mysql_fetch_object($result)) {
    echo "<tr>
        <td>$row->ID</td>
        <td>$row->CARMAKE</td>
        <td>$row->CARMODEL</td>
        <td>$row->FUELTYPE</td>
        <td>$row->TRANSMISSION</td>
        <td>$row->ENGINESIZE</td>
        <td>$row->DOORS</td>
        <td>$row->AMOUNT</td>
        <td>$row->AVAILABLE</td>
        <td>$row->DATEADDED</td>
        <td><a href='?rent=$row->ID'>Rent</a></td>
        </tr>";
}
echo "</table>";

Are you wondering how to access the values from the row of the table where the user clicks? — RonaldBarzell
– RonaldBarzell, Commented Dec 20, 2012 at 13:21
@user1161318 Yes I am, I've tried putting "$row->ID" where I've marked the code with XXX but can't seem to get that to work. — user1872666
– user1872666, Commented Dec 20, 2012 at 13:25
If you're going to do that, you'll need to put that section of code inside your loop where $row is set. — RonaldBarzell
– RonaldBarzell, Commented Dec 20, 2012 at 13:27
@user1161318 Okay, how would I go about doing this? Could you post a small example, as I'm fairly new to php — user1872666
– user1872666, Commented Dec 20, 2012 at 13:30
I was about to, but someone may have beaten me to it. Let me know if the answer doesn't address your needs, and I'll look at it again. — RonaldBarzell
– RonaldBarzell, Commented Dec 20, 2012 at 13:32

Howitzer · Accepted Answer · 2012-12-20 13:38:55Z

1

mysql_* is deprecated as of PHP 5.5.0, you should use something like PDO.

try {
    $DBH = new PDO('mysql:dbname=cardatabase;host=localhost', 'root', 'password');
} catch (PDOException $e) {
    echo 'Connection failed: ' . $e->getMessage();
}

$STH = $DBH->query("SELECT * FROM cars")->execute();

while ($row = $STH->fetch(PDO::FETCH_OBJ)) {
      echo "<tr>
<td>$row->ID</td>
<td>$row->CARMAKE</td>
<td>$row->CARMODEL</td>
<td>$row->FUELTYPE</td>
<td>$row->TRANSMISSION</td>
<td>$row->ENGINESIZE</td>
<td>$row->DOORS</td>
<td>$row->AMOUNT</td>
<td>$row->AVAILABLE</td>
<td>$row->DATEADDED</td>
<td><a href='?rent=".$row->ID."'>Rent</a></td>
</tr>";
    }

Edit: Just like @Skatox said!

answered Dec 20, 2012 at 13:38

Howitzer

215 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Skatox · Accepted Answer · 2012-12-20 13:31:34Z

0

I would do it like this:

<?php
$link = mysql_connect ("localhost", "root", "password");
mysql_select_db ("cardatabase");
$query = "SELECT * from cars";
$result = mysql_query ($query);

Get car information and store it

if($_GET['rent'])
{
$query_car = sprintf("SELECT * from cars WHERE ID=%s",$_GET['rent']); //Avoids sql injection
$rslt = mysql_query($query_car);
$car = mysql_fetch_object ($rslt)

Here you need to validate if there's no car

$rent = "INSERT INTO rentedcars VALUES('$car->ID','$car->CARMAKE','$car->CARMODEL','$car->FUELTYPE','$car->TRANSMISSION','$car->ENGINESIZE','$car->DOORS','$car->AMOUNT','$car->AVAILABLE','$car->DATEADDED')";
mysql_query($rent);
echo "<meta http-equiv='refresh' content='0;url=rent.php'/>";
}

Change it to while like @Vinoth Babu said:

while   ($row = mysql_fetch_object ($result)) 
{        
$row = mysql_fetch_object ($result);
echo "<tr>
<td>$row->ID</td>
<td>$row->CARMAKE</td>
<td>$row->CARMODEL</td>
<td>$row->FUELTYPE</td>
<td>$row->TRANSMISSION</td>
<td>$row->ENGINESIZE</td>
<td>$row->DOORS</td>
<td>$row->AMOUNT</td>
<td>$row->AVAILABLE</td>
<td>$row->DATEADDED</td>
<td><a href='?rent=$row->ID'>Rent</a></td>
</tr>";
}
print "</table>";
?>

I would recommend you to switch to MySQL PDO, it's safer and you'll get a better and secure code.

answered Dec 20, 2012 at 13:31

Skatox

4,27412 gold badges47 silver badges50 bronze badges

4 Comments

user1872666 Over a year ago

I implemented your new code, and it still doesn't seem to be inserting the data, see the new code in the edited question

Skatox Over a year ago

You'll need to add column names before VALUES part, this will work only if the order of the data is the same as the table definition.

user1872666 Over a year ago

The column names are before the values? Also, I just took the meta refresh out to find an error message that would have been there, but disappeared because it was refreshing Warning: mysql_fetch_object(): supplied argument is not a valid MySQL result resource in /Users/jstraw/Sites/rent.php on line 43

Skatox Over a year ago

That error is because there's no returned car due to SQL problems. Yes, you'll need to name the columns before the values, check @ goodmood comment to have more details.

echo_Me · Accepted Answer · 2012-12-20 13:33:19Z

0

you are missing the column names in your insert query

 $rent = "INSERT INTO rentedcars (id ,carmake, carmodel,fueltype,transmission, enginesize,doors,amount ,available, dateadded) 
           VALUES('xxx','xxx','".$carmodel."','XXX','XXX','XXX','XXX','XXX','XXX','XXX')"; 

                                   ^^^^^-------------i showed u exempel under

those XXX are values you get the from the inputs values

exemple

  <input  name= "car_model" id= "car_model" value="mercedes" >

then you get this value

 if (isset($_POST['car_model'])){ $carmodel = $_POST['car_model']}

and then use this value $carmodel in your sql

answered Dec 20, 2012 at 13:33

echo_Me

37.2k5 gold badges62 silver badges81 bronze badges

Collectives™ on Stack Overflow

MySQL insert row from HTML table into db table

3 Answers 3

Comments

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related