homepage Welcome to WebmasterWorld Guest from 54.196.197.153
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

    
Updating a record or two
Jamier101




msg:4498719
 2:33 am on Sep 23, 2012 (gmt 0)

Hello,

I've been working on a PHP script that drops records out of my database. I have requested that all entries that do not contain the word Yes in the sent column are dropped out and this seems to be working fine. The problems is that I can't seem to come up with or find a method of having a check box displayed against each entry so I can tick it and then do a global update of all the items that have been shipped. Ideally upon updating the records the page would reload therefore only showing what work is left to do. I'm hoping someone has experience of doing this and can hopefully point me in the right direction.

This is what I have so far:
<?php

// Get results from table
$result = mysql_query("SELECT * FROM $tbl_name")
or die(mysql_error());

echo "<table border='1'>";
echo "<tr> <th>ID</th> <th>First Name</th> <th>Surname</th> <th>Address1</th> <th>Address2</th> <th>Town/City</th> <th>County</th> <th>Post Code</th> <th>Request Date</th> </tr>";
// keeps getting the next row until there are no more to get
while($row = mysql_fetch_array( $result )) {
// Print out the contents of each row into a table
echo "<tr><td>";
echo $row['request'];
echo "</td><td>";
echo $row['firstname'];
echo "</td><td>";
echo $row['surname'];
echo "</td><td>";
echo $row['address1'];
echo "</td><td>";
echo $row['address2'];
echo "</td><td>";
echo $row['town'];
echo "</td><td>";
echo $row['county'];
echo "</td><td>";
echo $row['postcode'];
echo "</td><td>";
echo $row['date'];
echo "</td></tr>";
}

echo "</table>";

$num_rows = mysql_num_rows($result);
echo "$num_rows samples are awaiting shipment.<br>";

echo "Updated on: ";
echo date("l, F d, T h:i" ,time());
echo "<br>";
?>

 

swa66




msg:4498760
 8:51 am on Sep 23, 2012 (gmt 0)

By drop I think you want to say output ? not delete ?

Use the WHERE clause on your select to get only rows you're interested in.
E.g.
SELECT * FROM $tbl_name WHERE sent <> 'Yes' ;

Now there's some bad things in there:

"Yes" is better stored as a boolean instead of strings -> much less confusion possible (e.g. is "Yep" a Yes ? is "YES" ? yes ? YeS ? yES ? y ? Y ?

SELECT * : select only the columns you need instead of * (which dumps them all out of the database and pushes them into php data structures. Not only is it inefficient, but it's also not future proof. And unsafe: what if the database column is renamed ? You get no SQL error , yet your datastructure in php is now different from what you expect.

The mysql interface you use in php is obsolete. Use mysqli !

....

You can add a form on output to create a button "shipped" on every line that calls a php script with a GET parameter of the id of the order and that script can then update your database after proper validation.

swa66




msg:4498770
 10:27 am on Sep 23, 2012 (gmt 0)

Oh, sorry forgot to add: yes you can make a big form with checkboxes and update it all in one go, essentially you need to add a name on the fly to each checkbox, put them all in one form and submit it to a script that then checks all the values it is given and updates the fields as appropriate. It's not that hard to make, but I'd suggest to try the buttons first as those are simpler to make.

Jamier101




msg:4498772
 10:37 am on Sep 23, 2012 (gmt 0)

You are correct swa66, by drop I did mean output... it had gone 2:30am and I was out of tea so they brain wasn't fully functioning.

When the entry is made into the database I have told the form to input the 'sent' field with
$sent=strtoupper(No); so that there could be no confusion with typing.

As for generating the boxes on the fly, this is what truly confuses me as each box would have to be somehow directly linked to the record it needs to update :-s

Jamier101




msg:4498773
 10:41 am on Sep 23, 2012 (gmt 0)

In essence I'd like to have a check box at the end of each output in my table, select a shipped checkbox, then click update which would update all of the selected records, page refresh and Bob is my mothers brother :-)

swa66




msg:4498832
 2:55 pm on Sep 23, 2012 (gmt 0)

each box would have to be somehow directly linked to the record it needs to update :-s


That's why good database design adds an "id" column that's most often automatically updated and kept unique by the database.

you then print out a form when you send that id and value to a new script that gets the input from the form, validates it all and updates the database.

When the entry is made into the database I have told the form to input the 'sent' field with $sent=strtoupper(No); so that there could be no confusion with typing.

I'm thinking much longer term when I'm talking about databases: what if there's a new way to add data in some future time where the developer might not know that you rely on uppercase data only in that field ?

Now mixing addresses and delivery is also most likely a normalization issue.

Jamier101




msg:4499472
 10:26 pm on Sep 24, 2012 (gmt 0)

Unfortunately I'm about at my tether with my code not working, I've managed to get the checkboxes to display but I don't seem to be able to get them to update. I'm trying to request that the checked boxes are set to 1 when I click the submit button but it doesn't seem to work... any ideas?

<h1>Pending Sample Requests</h1>

<?php

// Get results from table
$result = mysql_query("SELECT * FROM $tbl_name WHERE sent=0")
or die(mysql_error());

echo "<table border='1'>";
echo "<tr> <th>ID</th> <th>First Name</th> <th>Surname</th> <th>Address1</th> <th>Address2</th> <th>Town/City</th> <th>County</th> <th>Post Code</th> <th>Request Date</th> <th>Sent</th> </tr>";
// keeps getting the next row until there are no more to get
while($row = mysql_fetch_array( $result )) {
// Print out the contents of each row into a table
echo "<tr><td>";
echo $row['request'];
echo "</td><td>";
echo $row['firstname'];
echo "</td><td>";
echo $row['surname'];
echo "</td><td>";
echo $row['address1'];
echo "</td><td>";
echo $row['address2'];
echo "</td><td>";
echo $row['town'];
echo "</td><td>";
echo $row['county'];
echo "</td><td>";
echo $row['postcode'];
echo "</td><td>";
echo $row['date'];
echo "</td><td>";
echo "<input type='checkbox' name='update' value='1'>";
echo "</td></tr>";
}

echo "</table>";

echo "<input type='button' name='submit' value='Submit'>";

function update()
{
if (isset($_POST['Submit']) && $_POST['Submit'] == 'Submit')
{
$update=($_POST['update']);
$sql = mysql_query("UPDATE $tbl_name SET sent = 1 WHERE ". $update ." = 1");
}
}
echo "<br>";

$num_rows = mysql_num_rows($result);
echo "$num_rows samples are awaiting shipment.<br>";
echo "Updated on: ".date("l, F d, T h:i" ,time());
echo "<br>";

?>

Jamier101




msg:4499478
 10:47 pm on Sep 24, 2012 (gmt 0)

I've tried changing a few things but still no joy!

<?php

// Get results from table
$result = mysql_query("SELECT * FROM $tbl_name WHERE sent=0")
or die(mysql_error());

echo "<table border='1'>";
echo "<tr> <th>ID</th> <th>First Name</th> <th>Surname</th> <th>Address1</th> <th>Address2</th> <th>Town/City</th> <th>County</th> <th>Post Code</th> <th>Request Date</th> <th>Sent</th> <th>Print</th> </tr>";
// keeps getting the next row until there are no more to get
while($row = mysql_fetch_array( $result )) {
// Print out the contents of each row into a table
echo "<tr><td>";
echo $row['request'];
echo "</td><td>";
echo $row['firstname'];
echo "</td><td>";
echo $row['surname'];
echo "</td><td>";
echo $row['address1'];
echo "</td><td>";
echo $row['address2'];
echo "</td><td>";
echo $row['town'];
echo "</td><td>";
echo $row['county'];
echo "</td><td>";
echo $row['postcode'];
echo "</td><td>";
echo $row['date'];
echo "</td><td>";
echo "<input type='checkbox' name='update' value='". $request ."'>";
echo "</td><td>";
echo "<input type='checkbox' name='print' value=''>";
echo "</td></tr>";
}

echo "</table>";

echo "<input type='button' name='Submit' value='Submit'>";

function update()
{
if (isset($_POST['Submit']) && $_POST['Submit'] == 'Submit')
{
$update=($_POST['update']);
$sql = mysql_query("UPDATE $tbl_name SET sent = 1 WHERE ". $update ." = ". $request ." ");
}
}

Jamier101




msg:4499484
 11:03 pm on Sep 24, 2012 (gmt 0)

I've even tried:

function update()
{
if (isset($_POST['Submit']) && $_POST['Submit'] == 'Submit')
{
$update = ($_POST['update']);
$request = ($row['request']);
$sql = mysql_query("UPDATE sample_requests SET sent = 1 WHERE ". $request ." = ". $update ." ");
}
}

swa66




msg:4499486
 11:05 pm on Sep 24, 2012 (gmt 0)

You have a <from> tag in there ?
-> if not add it.
-> make sure it point to another php script not the one doing the output, you're making it far too difficult to start with.

You should read [webmasterworld.com...] : it shows how to get the form to submit an array of checked values , an dthen process those in the script referenced from the <form> tag.

Jamier101




msg:4499487
 11:09 pm on Sep 24, 2012 (gmt 0)

Oh, so your saying that on submit I should call another PHP script, do all of the updating and then return to the page I left.

swa66




msg:4499608
 7:46 am on Sep 25, 2012 (gmt 0)

Yep. After you've done updating, just output the location header of the first script.

I'm sure you eventually can fold it all into one script, but it complicates things and it might confuse you. It's easier to make it separate and only try to detect if it was a post or not in a later stage.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved