Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2010
    Posts
    16

    Question Unanswered: Need help with an array

    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?

  2. #2
    Join Date
    May 2008
    Posts
    277
    Quote Originally Posted by RRT View Post
    Does that make sense?
    No. A more concrete example of what you're having problems with would help.

  3. #3
    Join Date
    Jan 2010
    Posts
    16
    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...

  4. #4
    Join Date
    May 2008
    Posts
    277
    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.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •