A little POST and MySQL update question (combining search and multiple record update)

christillis

Hello all!

Well after scratching my head for too long, I give in, I've got to ask or I'll go crazy!

I've been fiddling with PHP/MySQL again recently and although I successfully get a search of the db via a form field and have found a way* of adding a checkbox and update multiple records (a popular request I think!), my next hurdle is combining them.

I was hoping I could mash the script together (logically with my limited knowledge) to produce a 3 stage script. Page1 would send Page2 the search term (works so far) and then Page2 would send itself(and the db) those recorded selected for updating.

So far, when I try to I'm getting many errors, and I believe they occur because the script is trying to get information that's not there (from the search). Also it's because I've tried to modify existing code and I'm really guessing now :-(

Here's the code on the second page, the first page simply has a $_POST in it and then the second page collects that info:

<?php
// Connect to server and select databse.
mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");


if(isset($_POST['projno-search'])) $search = $_POST['projno-search'];
if(isset($_POST['checkbox'])){$checkbox = $_POST['checkbox'];
if(isset($_POST['activate'])?$activate = $_POST["activate"]:$deactivate = $_POST["deactivate"])
$id = "('" . implode( "','", $checkbox ) . "');" ;
$sql="UPDATE $tbl_name SET invoiced = '".(isset($activate)?'Y':'N')."' WHERE entryref IN $id" ;
$result = mysql_query($sql) or die(mysql_error());
}

$sql="SELECT * FROM $tbl_name WHERE projno=$search";
// $sql="SELECT * FROM $tbl_name ";
$result=mysql_query($sql);

$count=mysql_num_rows($result);

?>


<head>

<title>Checkboxes</title>

</head>
<body>

<table width="400" border="0" cellspacing="1" cellpadding="0">
<tr>
<td><form name="frmactive" method="post" action="">
<table width="400" border="0" cellpadding="3" cellspacing="1">
<tr>
<td colspan="5"><input name="activate" type="submit" id="activate" value="Invoiced" />
<input name="deactivate" type="submit" id="deactivate" value="Not Invoiced" /></td>
</tr>
<tr>
<td>&nbsp;</td>
<td colspan="4"><strong>Update multiple rows in mysql with checkbox</strong> </td>
</tr><tr>
<td align="center"><input type="checkbox" name="allbox" /></td>
<td align="center"><strong>Entry Ref</strong></td>
<td align="center"><strong>Project No.</strong></td>
<td align="center"><strong>Description</strong></td>
<td align="center"><strong>Hours</strong></td>
<td align="center"><strong>Invoiced</strong></td>
</tr>
<?php
while($rows=mysql_fetch_array($result)){
?>
<tr>
<td align="center"><input name="checkbox[]" type="checkbox" id="checkbox[]" value="<?php echo $rows['entryref']; ?>"></td>
<td><?php echo $rows['entryref']; ?> </td>
<td><?php echo $rows['projno']; ?> </td>
<td><?php echo $rows['description']; ?> </td>
<td><?php echo $rows['hours']; ?> </td>
<td><?php echo $rows['invoiced']; ?> </td>
</tr>
<?php
}
?>
<tr>
<td colspan="5" align="center">&nbsp;</td>
</tr>
</table>
</form>
</td>
</tr>
</table>
</body>
</html>

Here's the page when I click on one of the invoice buttons (invoiced/not invoiced):

Notice: Undefined variable: search in C:\wamp\www\Version2\klw-checkboxes.php on line 22

Line 22: $sql="SELECT * FROM $tbl_name WHERE projno=$search";

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in C:\wamp\www\Version2\klw-checkboxes.php on line 26

Line 26: $count=mysql_num_rows($result);

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in C:\wamp\www\Version2\klw-checkboxes.php on line 58

Line 58: while($rows=mysql_fetch_array($result)){

It might be worth knowing that security isn't an issue as this script is run offline via WAMPServer (a brilliant freebee for testing!).

Well, I really look forward to any comments, so thank you in advance! I'm guessing it's because I've interferred too much, using my inexperienced hands!

I found a script close to the script I wanted on the net and tweaked it to work for me.

ixalmida

A couple of suggestions:
1) Make things easy on yourself and do an "extract($_POST)" statement so you can use the POST variables as local variables. It saves a bunch of typing.

2) You need to do more validation than just an isset() statement. Assuming search is a text box, you absolutely need to make sure that the "search" is database-safe. Assuming you followed step 1 above, do "$search = mysql_real_escape_string($projno-search)" or if it is numeric, use an is_numeric() statement to make sure it is numeric.

3) If $search is a string, you need to enclose it in single-quotes in the query. If not, then you should make sure it is numeric before you run the query (i.e. - if the search box is left entry, $search will just be an empty string). So to MySQL, your query looks like this:

SELECT * FROM Table WHERE projno =

...so of course the result resource would not be valid. Again, if you don't check that it is numeric, a user could just enter "*" in the search box and pull up everything.

christillis

Hi ixalmida,

First of all, thank you very much for responding to my newbie post, it's truly appreciated!

I'll certainly look into that extract($_POST) method of getting POST info.

At the moment, security isn't an issue, but I do value your comments on this matter. So you can have the option of boredom, I've put the background of my little project at the bottom of this post.

It is numeric data that's being passed to the $search variable (a 5 number reference), is there a way of cancelling the "SELECT * FROM Table WHERE projno =nothing" issue? So if I add the single quotes, will that resolve the issue of not having a value for $search? You don't have to answer as I'll try it tomorrow!

Thanks again mate, much appreciated!

Optional reading:

Currently I record time spent on drawing work (I'm an Architect's Assistant by day) by putting it in a spreadsheet within a project folder. When it's time to invoice, an admin staff locates the project folder and prints it off, then creates an invoice using Word based on the printed spreadsheet. It's an incredibly inefficient system as you will know and I just wanted to make it a bit quicker for me to record time & the admin staff to find that time to invoice it. If I become more proficient at PHP/MySQL I'll take it a step further! At the moment, I've installed WAMPServer on my PC and set it up so that it's accessible via LAN computers (a mix of Mac's and PC's). I'm not adding security at this stage firstly because I'm very new at this, but also because it's not needed in the environment it's being used in. That's it really!

ixalmida

extract() isn't really a method - more of a shortcut. It extracts an array into $key = $value variables. So extract($POST) would create all the local variables named after the submitted form fields...i.e.- $POST['search'] becomes just $search. It saves a lot of typing.

(Note: You can use extract($rows) in your while() loop to save some typing as well.)

If the database is expecting a number for that particular column, you don't need to single-quote it. But just to ensure that your page won't fail, you should do something like this:

<?php
// Connect to server and select databse.
mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");

extract($_POST);
if(empty($projno-search)) $projno-search = 0;
if(! isset($activate) || empty($activate))
    $activate = "Y";
else
    $activate = "N";
$id = "('" . implode( "','", $checkbox ) . "');" ;
$sql="UPDATE $tbl_name SET invoiced = '$activate' WHERE entryref IN $id" ;
$result = mysql_query($sql) or die(mysql_error());
}

$sql="SELECT * FROM $tbl_name WHERE projno=$search";
// $sql="SELECT * FROM $tbl_name ";
$result=mysql_query($sql);
$count=mysql_num_rows($result);
?>

Note that if $projno-search doesn't have a value then I explicitly set it to zero. But again, you could still have trouble if someone inputs a letter instead of a number and you try to insert it into your numeric field. That's why I'd suggest using "! is_numeric()" instead of "empty()" if you're expecting a number.

christillis

Hello again ixalmida,

Thank you for your patience on this one, again it's much appreciated.

I inserted your code into it (removing the previous code) and it came up with:

"Parse error: parse error in XXX.php on line 28"

On line 28 it's just a '}'. I tried adding a '{' before it in a few places, but it didn't remedy the problem. I also changed $projno-search to $projno_search in case the dash caused problems, I changed it on the previous submitting page too (the one that points to this main page).

I was just wondering, but should:

"....WHERE projno=$search"

"...WHERE projno=$projno_search" ?

I also wondered if:

extract($_POST);

....Needs to be:

extract($_POST['projno_search');

I did try, but just more errors popped up when it was processed.

Please forgive my lack of knowledge, we all have to start somewhere! Looking forward to your reply.

bradgrafelman

A note about using [man]extract/man (at least, without specifying the optional 2nd/3rd parameters): extract()'ing external variables (e.g. from $_POST) basically mimicks the same behavior that the (highly) deprecated register_globals directive did. Thus, doing this will expose you to the same security risks that register_globals did. See this manual page for more information: [man]security.globals[/man]. For this reason, I would recommend that you never use extract() (at least, not without intelligently picking sane second/third argument values) on incoming data.

I personally never use extract() because it often leads to variable collision or just plain confusion. If you're looping through database rows, does $username refer to the supplied username sent in a form, the username in the URL/query string, a row in the database, ... ?? Now try answering that same question with variables such as $_GET['username'] or $row['username']. Much easier, isn't it?

ixalmida

If you only use extract() for POST/GET and row arrays and refer to session and cookie variables explicitly (as only makes sense), then there is almost no risk of collision or abuse. Include sensible, unique variable names and data scrubbing, and the risk goes to nil.

I'll grant you that a novice should probably use the second/third argument.

Crowly

"Parse error: parse error in XXX.php on line 28"

On line 28 it's just a '}'

The line number isnt always excat, depends a bit on what error was made, try to look at some of the lines above as well. If its not excat is rarely a long way off 😉

Also when you use variables in strings, put { } around them to help php see where the variable start and end.

$sql="SELECT * FROM {$tbl_name} WHERE projno={$search}";

// or 
$sql="SELECT * FROM ".$tbl_name." WHERE projno=".$search;

// or 
$sql=sprintf("SELECT * FROM %s WHERE projno=%s",$tbl_name,$search);

Depending a bit on what type of search you do, you might need to do WHERE projno LIKE {$search}