Need help with an array

RRT · #1 (**permalink**) 02-12-12, 23:00

I am pulling data out of my database, row by row, using the following similar PHP syntax:

Code:

$query1 = 'SELECT field1, field2, field3 FROM tableQ';

$result = pg_query($query1) or die("Query failed: " . pg_last_error());

while ($line = pg_fetch_array($result, null, PGSQL_ASSOC)) {

    foreach ($line as $col_value) {
         echo "\t\t<td>$col_value</td>\n";
	}

    }

Now that I'm doing it like that, I'm running into some roadblocks doing data edits via PHP pages and this method seems like a poor design. Here's my problem: since I setup the querying of the database this way, I have very little control over displaying the data and then being able to select fields that I want to modify, delete, or change - from the presentation venue.

Wouldn't it be a better design to do a SELECT statement of the database, then pump that data from the query into some type of array, then iterate over the array to display it out, maintaining the ability to grab or alter data in array field field2[6], for example? Does that make sense?

I'm just brainstorming out loud at this point... How would you attempt to implement something like this for example?

futurity · #2 (**permalink**) 02-15-12, 10:58

Quote:

Originally Posted by RRT

Does that make sense?

No. A more concrete example of what you're having problems with would help.

RRT · #3 (**permalink**) 02-16-12, 19:04

Well, for example, I want to be able to check a checkbox that would select an entire row within a table and then be able to delete it. How do I correspond the checkbox to execute a SQL statement? I know how to write the SQL statement to delete, just can't figure out the logic to connect the checkbox to it...

futurity · #4 (**permalink**) 02-16-12, 20:17

Just use the value of the row's primary key as the value of the checkbox. When/if that value gets submitted back, use it your populate your sql statement.

This could make handling multi-column primary keys troublesome, so a single-column, surrogate key will likely be useful. Note that you must still enforce the natural key with a UNIQUE constraint even if you use a surrogate key.

Here's a simple example:

PHP Code:


			
$db = pg_connect('dbname=mydb');



$sql = 'SELECT id, username, first_name, last_name FROM my_table';

$result = pg_query($db, $sql);



echo "<form action=\"my_url\" method=\"POST\">\n"

echo "<p>Select the users you want to delete:</p>\n";

echo "<ul>\n";



while ($row = pg_fetch_assoc($db, $result)) {

    echo '<li><label><input type="checkbox" name="delete[]" value="',

        $row['id'], '">', $row['username'], ' &mdash; ', $row['first_name'],

        ' ' , $row['last_name'], "</label></li>\n";

}



echo "</ul>\n";

echo "</form>\n";

Now to process the form submittal:

PHP Code:


			
if ('POST' == $_SERVER['REQUEST_METHOD']) {

    $sql = sprintf(

        'delete from my_table where id in (%s)',

        implode(', ', $_POST['delete'])

    );

    pg_query($db, $sql);

}

Note this doesn't do any input sanitization/validation or protect from SQL injection attacks, all of which you will want to do. But it's enough of an example to show you how to go about this.

Internet Services

Web Development

Digital Marketing

Consumer Tech