How to select a row(s) from mysql table using checkbox (PHP)

Question

Hi I have a page that displays all of the contents of my table. Alongisde each row of the table I also have a column containing a checkbox. When the user selects one or more rows by ticking the checkbox and pressing the submit button, I want just those rows to appear in a table on the next page (buy.php). I know it is basically a select statement per row that is selected. But I dont know how to approach this. Can anybody help? Thanks

        <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
    "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
    <meta http-equiv="content-type" content="text/html; charset=utf-8" />
    <title>Shopping</title>
</head>
<body>
<form action='buy.php' method='post'>
<input type='submit' value='Submit' />
</form> 
<h1>Buy</h1>
<?php // Script 12.7 - sopping.php

$db = mysql_connect('localhost', '#####', '#####');
mysql_select_db('shopping', $db);

$query = 'SELECT * FROM Items';

if ($r = mysql_query($query, $db)) { 
    print "<form>
    <table>";
    while ($row = mysql_fetch_array($r)) {
        print 
        "<tr>
        <td>{$row['ID']}</td>
        <td>{$row['Name']}</td>
        <td>{$row['Cost']}</td>
        <td><input type='checkbox' name='buy[{$row['ID']}] value='buy' /></td>
        </tr>";
    }
    print "</table>
    </form>";

} else { 
    print '<p style="color: blue">Error!</p>';
} 

mysql_close($db); // Close the connection.

?>
</body>
</html>

EDIT: I tried the suggestion below and I did not get a table. Just the title of the page appeard:

            <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
    "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
    <meta http-equiv="content-type" content="text/html; charset=utf-8" />
    <title>Confirmation</title>
</head>
<body>

<h1>Order Details</h1>
<?php // Script 12.7 - buy.php

$db = mysql_connect('localhost', '#####', '#####');
mysql_select_db('shopping', $db);

foreach($_POST['buy'] as $item) {

$query = 'SELECT * FROM Items WHERE ID = $item';

if ($r = mysql_query($query, $db)) { 
    print "<table>";
    while ($row = mysql_fetch_array($r)) {
        print 
        "<tr>
        <td>{$row['ID']}</td>
        <td>{$row['Name']}</td>
        <td>{$row['Cost']}</td>
        </tr>";
    }
    print "</table>";

} else { 
    print '<p style="color: blue">Error!</p>';
} 
}

mysql_close($db);

?>
</body>
</html>

David · Accepted Answer · 2012-03-31 16:06:45Z

6

You need to create a dynamic SQL query.

1) I suggest you change your checkbox to the following

<input type='checkbox' name='buy[]' value='{$row['ID']}' />

2) Loop through all of the buy options

<?php
  foreach($_POST['buy'] as $item) {
    // Append the ID (in the $item variable) to the SQL query, using WHERE `ID` = $item OR `ID` = $item and so on
  }
?>

UPDATE:

<?php // Script 12.7 - buy.php

$db = mysql_connect('localhost', '#####', '#####');
mysql_select_db('shopping', $db);

$query = 'SELECT * FROM Items WHERE ';

$item_count = count($_POST['buy']);

for($i = 0; $i < $item_count; $i++) {
  $itemid = (int)mysql_real_escape_string($_POST['buy'][i]); // Secures It
  $query .= '`ID` = '.$itemid;
  if($i +1 < $item_count) {
    $query .= ' OR ';
  }
}

if ($r = mysql_query($query, $db)) { 
    print "<table>";
    while ($row = mysql_fetch_array($r)) {
        print 
        "<tr>
        <td>{$row['ID']}</td>
        <td>{$row['Name']}</td>
        <td>{$row['Cost']}</td>
        </tr>";
    }
print "</table>";
mysql_close($db);
?>

edited Mar 31, 2012 at 16:06

answered Mar 31, 2012 at 12:30

David

4,3611 gold badge24 silver badges29 bronze badges

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

8 Comments

Lawrence Cherone Over a year ago

You should also note that some checks should be made on the value from the post and protect from sql injections :)

M9A Over a year ago

do i add the step 2 part in the coding for the new page? Also is this statement correct: SELECT * FROM Items WHERE 'ID' = $item

David Over a year ago

Step 2 is on the new page / process order page. That sql statement is incorrect. 'ID' should be `ID` , use the key with ~. Column names use ` values use ' (single quote)

M9A Over a year ago

can i copy the table code from above into the loop so they display in a table

David Over a year ago

If you change the SQL query, yes.

|

Al-mahbub Khan · Accepted Answer · 2015-05-15 12:34:28Z

0

// this checkbox use

//buy.php foreach($_POST['buy'] as $item) {

$query = "SELECT * FROM Items WHERE ID = $item"; //use " "

// try it if any problem please carefully look you database table // download file and try it 2file with table sql data

// https://copy.com/8ZsBAFj7LvypLJkK

answered May 15, 2015 at 12:34

Al-mahbub Khan

3092 silver badges10 bronze badges

Comments

Arnab Nandy · Accepted Answer · 2015-05-15 12:45:27Z

0

// this checkbox use 
<input type='checkbox' name='buy[]' value='{$row['ID']}' />


//buy.php
foreach($_POST['buy'] as $item) {

$query = "SELECT * FROM Items WHERE ID = $item";
//use " "

//   try it if any problem please carefully look you database table
// download file and try it 2file with table sql data

// https://copy.com/8ZsBAFj7LvypLJkK

edited May 15, 2015 at 12:45

Arnab Nandy

6,8025 gold badges50 silver badges52 bronze badges

answered May 15, 2015 at 12:26

Al-mahbub Khan

3092 silver badges10 bronze badges

1 Comment

Al-mahbub Khan Over a year ago

// this checkbox use <input type='checkbox' name='buy[]' value='{$row['ID']}' />

Collectives™ on Stack Overflow

How to select a row(s) from mysql table using checkbox (PHP)

3 Answers 3

8 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

8 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related