Help understanding PHP query

Grant Holmes · January 14, 2008

I have a page that shows a dynamic number of records. The simple thing I'm doing here is "Activating" inactive records; changing a 0 to a 1 in my field called "Active".

On my display page, the iINactive records are only shown and there is a checkbox that accurately shows the status of the field.

I've figured out (with your help) how to select all the checkboxes and now want to use a button that calls a page to update the records that were selected.

The code on that page is:

<?php $DOCROOT = $_SERVER['DOCUMENT_ROOT'] ;

// start query
// This line borrowed from another page doing the same thing, but I don't have ALL these fields...
// the table "birthdays" and field Active do exist among others...
$qry = "UPDATE birthdays SET Active='1' WHERE COMMENTCLIENTID='$clientid' AND (COMMENTID='$id'";

// loop through each comment and add it to the query to delete it
for ($i = 1; $i < count($chk_msg); $i++)
{
		// get id
	$id = $chk_msg[$i];

		// add this id to the query
	$qry .= " OR COMMENTID='$id'";
}
$qry .= ")";

// run query
mysql_query ( $qry, $link ) or die ( "Query to activate failed: $qry." );

// close connection 
mysql_close ($link);

// go to Admin Panel
load_page ( "requests_inactive_test.php?prod_id=$prod_id" );			 

?>

See commented line. I borrowed this from another site of mine that I had developed. The line that appears to me to do all the work is:

$qry = "UPDATE birthdays SET ACTIVE='1' WHERE COMMENTCLIENTID='$clientid' AND (COMMENTID='$id'";

But I don't have all those fields.

Can someone explain what this line even means/does in plain English, or is this enough information to help me edit the code to update my records. The ONLY field I want to change/update is the ACTIVE field.

revraz · January 14, 2008

It Updates the table birthdays and changes the field Active to 1 that meets the ID criterias after the WHERE

Grant Holmes · January 14, 2008

Revraz, thanks for your help...

can you further break that down :-\

COMMENTCLIENTID='$clientid' AND (COMMENTID='$id'"; ???

from my results page, I have:

<?php
$checked = $Active ? "checked" : "";
echo "<input type=\"checkbox\" name=\"foo\" $checked>";
?>

That shows the checkbox, and that was in a loop getting the results like:

<?php
$i=0;
while ($i < $num) {
$Active=mysql_result($result,$i,"Active");

I know CLIENTID and COMMENTCLIENTID and COMMENTID are no longer valid.

I assume "id" is.

How would I modify that line to update my current table?

revraz · January 14, 2008

Show the table layout/field names.

Grant Holmes · January 14, 2008

<?php
$i=0;
while ($i < $num) {
$Active=mysql_result($result,$i,"Active");
$firstname=mysql_result($result,$i,"Contact_Info_FirstName");
$lastname=mysql_result($result,$i,"Contact_Info_LastName");
$city=mysql_result($result,$i,"Contact_Info_City");
$state=mysql_result($result,$i,"Contact_Info_State");
$zip=mysql_result($result,$i,"Contact_Info_ZipCode");
$country=mysql_result($result,$i,"Contact_Info_Country");
$email=mysql_result($result,$i,"Contact_Info_Email");
$Bfirstname=mysql_result($result,$i,"Birthday_Info_FirstName");
$Blastname=mysql_result($result,$i,"Birthday_Info_LastName");
$Bcity=mysql_result($result,$i,"Birthday_Info_City");
$Bstate=mysql_result($result,$i,"Birthday_Info_State");
$Bzip=mysql_result($result,$i,"Birthday_Info_ZipCode");
$Bcountry=mysql_result($result,$i,"Birthday_Info_Country");
$Date=mysql_result($result,$i,"DateEntered");
$id=mysql_result($result,$i,"id"); 

?>
</form>
<tr> 
            <td valign="top"><input type="hidden" name="ud_id" value="<? echo $id; ?>">
            <STRONG><?php echo "$firstname $lastname"; ?></STRONG><BR><a href="mailto:<?php echo "$email"; ?>"><?php echo "$email"; ?></a></TD>
            <TD valign="top"><?php echo "$city"; ?></TD>
            <TD valign="top"><?php echo "$state"; ?></TD>	
            <TD valign="top"><?php echo "$zip"; ?></TD>			   	   
            <TD valign="top"><?php echo "$country"; ?></TD>	   		   
            <TD valign="top"> <?php echo "$Bfirstname"; ?></TD>
            <TD valign="top"> <?php echo "$Blastname"; ?></TD>
            <TD valign="top"><?php echo "$Date"; ?></TD>				   
            <TD valign="top"> <B>Active?: 
<?php
$checked = $Active ? "checked" : "";
echo "<input type=\"checkbox\" name=\"foo\" $checked>";
?>
 </td>
            <TD valign="top"><CENTER> <a href='requests_edit.php?id=<? echo "$id"; ?>'>edit</a><BR><A href="#top"><IMG src="images/uparrow.gif" border="0" hspace="2" vspace="4" /></A><A href="#bottom"><IMG src="images/downarrow.gif" border="0" hspace="4" vspace="4" /></A></CENTER></TD>
</TD>
</tr>
<?php
++$i;
} 
echo "</table>";
?>

cooldude832 · January 14, 2008

have you heard of the mysql_fetch_assoc() or mysql_fetch_array function save you a lot of hassle with all those mysql_result you have

Grant Holmes · January 14, 2008

Nope. I am sssoooooooo inexperienced in this... well anyhow, no. Slowly (very slowly) I understand more and more, but really SUCK at complex stuff like this and Algebra. Ask me to sing or speak in front of 10,000 people and I'm in MY element.

Most of what I have has been developed for me and I borrow code from this page and that because I know it works, not because I know what I'm doing. I WILL say that I've learned enough to edit here and there.

How would I modify that to save overhead?

cooldude832 · January 14, 2008

go to php.net and see what mysql_Fetch_assoc() does and then see why you are wasting a ton of resources.

Grant Holmes · January 14, 2008

"saying something doesn't make it so" or

"Reading something doesn't mean you'll understand what it said!!"

Where I don't know enough about any of this for that information to help me. It's not that I don't appreciate the help, I have nothing to base that help on. Does that make sense?

Anyhow, Can someone help me understand how to alter the code above? I'll get to the FETCH thing later!! (maybe another lifetime)

revraz · January 14, 2008

That's not the whole page of code right? Post it all if not.

Psycho · January 14, 2008

Because you are using a dynamic number of records I would suggest not using a checkbox. The value of a checkbox is only sent to the processing page if the checkbox is checked. So, you would have no direct way to know which options the user unchecked. You would have to work around it by sending a list of all the options that the user was given to the processing page. You may want to consider using a two-option radio group for each option.

One the processing page you could loop through all the received options and create two arrays: active IDs and inactive IDs. Then run the following two queries:

<?php
$query = "UPDATE table SET active = 1 WHERE id in (" . implode(',',$activeIDAry) . ")";
$query = "UPDATE table SET active = 0 WHERE id in (" . implode(',',$inactiveIDAry) . ")";
?>

Grant Holmes · January 14, 2008

The code requested is below.

<?php include_once("security/SECsecurity.php"); ?>
<?php $DOCROOT = $_SERVER['DOCUMENT_ROOT'] ; ?>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<HTML>
<HEAD>
<TITLE>test page Requests</TITLE>
    <META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
   <META NAME="GOOGLEBOT" CONTENT="NOARCHIVE">
    <META NAME="ROBOTS" CONTENT="NONE">
    <LINK REL="stylesheet" TYPE="text/css" HREF="text.css">
<script src="JS/sorttable.js"; ?></script>
<script language="JavaScript" type="text/javascript">
<!--
	function selectall(what)
	{
	 var caption;		
	 with (document.editcomments) {

	 if (all_selected.value == 1)
	 {

			 caption = "Check All"			 
			 all_selected.value = 0;
	 }
	 else
	 {


			 caption = "Uncheck All";
			 all_selected.value = 1;
	 }

	 for (var i=0; i < elements.length; i++) 
	 {
	 		  if (elements[i].type == 'checkbox')
						 if (all_selected.value == 1) elements[i].checked = true;
						 		else elements[i].checked = false;
	 		  else if (elements[i].type == 'button' && elements[i].name == "SelectAllBtn")
					 elements[i].value = caption;
	 }
	 }

	}
//-->
</script>
</HEAD>
<BODY>
<a name="top"></a>
<?php
include("dbinfo.inc.php");
mysql_connect(mysql,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
          
$query="SELECT * FROM birthdays WHERE Event='test' AND Active='0'";
$result=mysql_query($query);

$num=mysql_numrows($result); 

mysql_close();

echo "<A name=\"top\"></A><b><center><H1>Page Title \"<I>Inactive Requests</I>\":</H1>";
?>

<center><div style='width:100%; background-color:silver; text-align:right'>
  <?php SECShowAdminLink(); ?>
   
  <?php SECShowLogoutLink(); ?>  <A href="data.php">Main Data Page</A>  
</div>
<center><div style='width:100%; background-color:#ffe4c4; text-align:center'><BR>This page is showing <I>INACTIVE</I> Requests</B><BR><A href="requests.php">view <I>ALL</I> </A>   <A href="requests_active.php">view active only</A></div></center>


<table border="1" cellspacing="2" cellpadding="3" class="sortable"> 
<tr> 
	<TH valign="top">From:</TH>
	<TH valign="top">City</TH>	
	<TH valign="top">State</TH>			
	<TH valign="top">Zip</TH>			
	<TH valign="top">Country</TH>				
         <TH valign="top">Artist Name</TH>
         <TH valign="top">Song Title</TH>
         <TH valign="top">Submitted</TH>			
         <TH valign="top">Status</TH>	
         <TH valign="top"> </TH>		
</tr>

<?php
$i=0;
while ($i < $num) {
$Active=mysql_result($result,$i,"Active");
$firstname=mysql_result($result,$i,"Contact_Info_FirstName");
$lastname=mysql_result($result,$i,"Contact_Info_LastName");
$city=mysql_result($result,$i,"Contact_Info_City");
$state=mysql_result($result,$i,"Contact_Info_State");
$zip=mysql_result($result,$i,"Contact_Info_ZipCode");
$country=mysql_result($result,$i,"Contact_Info_Country");
$email=mysql_result($result,$i,"Contact_Info_Email");
$Bfirstname=mysql_result($result,$i,"Birthday_Info_FirstName");
$Blastname=mysql_result($result,$i,"Birthday_Info_LastName");
$Bcity=mysql_result($result,$i,"Birthday_Info_City");
$Bstate=mysql_result($result,$i,"Birthday_Info_State");
$Bzip=mysql_result($result,$i,"Birthday_Info_ZipCode");
$Bcountry=mysql_result($result,$i,"Birthday_Info_Country");
$Date=mysql_result($result,$i,"DateEntered");
$id=mysql_result($result,$i,"id"); 

?>
</form>
<form name="editcomments" action="<?php print "$PHP_SELF"; ?>"  method="POST" >
<input type=hidden name="all_selected" value="0">
<tr> 
            <td valign="top"><input type="hidden" name="ud_id" value="<? echo $id; ?>">
            <STRONG><?php echo "$firstname $lastname"; ?></STRONG><BR><a href="mailto:<?php echo "$email"; ?>"><?php echo "$email"; ?></a></TD>
            <TD valign="top"><?php echo "$city"; ?></TD>
            <TD valign="top"><?php echo "$state"; ?></TD>	
            <TD valign="top"><?php echo "$zip"; ?></TD>			   	   
            <TD valign="top"><?php echo "$country"; ?></TD>	   		   
            <TD valign="top"> <?php echo "$Bfirstname"; ?></TD>
            <TD valign="top"> <?php echo "$Blastname"; ?></TD>
            <TD valign="top"><?php echo "$Date"; ?></TD>				   
            <TD valign="top"> <B>Active?: 
<?php
$checked = $Active ? "checked" : "";
echo "<input type=\"checkbox\" class=\"group1\" name=\"foo\" $checked>";
?>
 </td>
            <TD valign="top"><CENTER> <a href='requests_edit.php?id=<? echo "$id"; ?>'>edit</a><BR><A href="#top"><IMG src="images/uparrow.gif" border="0" hspace="2" vspace="4" /></A><A href="#bottom"><IMG src="images/downarrow.gif" border="0" hspace="4" vspace="4" /></A></CENTER></TD>
</TD>
</tr>
<?php
++$i;
} 
echo "</table>";
?>
<input name="SelectAllBtn" type=button value='Check All' onClick="javascript:selectall(this)" >
<input type="button" value="Activate Selected" onClick="javascript:editcomments.action='requests_activate.php?<?php print "prod_id=$prod_id"; ?>';editcomments.submit();">
<input type="button" value="Deactivate Selected" onClick="javascript:editcomments.action='requests_deactivate.php?<?php print "prod_id=$prod_id"; ?>';editcomments.submit();">
<input type="button" value="Remove Selected" onClick="javascript:editcomments.action='confirmremovecomments.php?<?php print "prod_id=$prod_id"; ?>';editcomments.submit();">
<P><A href=\"#top\">Back to top</A></P></B><BR>
<BR><BR><BR>
           <?php include "include/footer.php"; ?>	
	  <a name="bottom"></a>
</BODY>
</html>

This is a second test page, in that the javascript above isn't yet selecting the checkboxes. Another page I had did that.

I'm sorry gang. If I'm just wasting your time, tell me. I've asked a couple guys I know locally that can do this to help for $$ and are slow to respond. Trying to do it myself and as you can likely tell, is prolly beyond my reach.

Sign In

Help understanding PHP query

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information