DaniWeb Forum Index > Web Development > PHP

Multiple SQL Queries in PHP page

Please support our PHP advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

•

Join Date: Oct 2008

Posts: 5

Reputation: tkmc is an unknown quantity at this point

Solved Threads: 0

tkmc

Offline

Newbie Poster

Multiple SQL Queries in PHP page

Oct 10th, 2008

Hi all,

I have been racking my brain today, trying to get a page to run 2 queries.

Basically i have a page that has a form on it and there is a bunch of numbers in one of the drop down lists that comes from a table called cab_eticket_numbers this and the rest of the data on this form is posted to a MySQL table called cab_details, which works all good. However I also need to update a flag in the cab_eticket_numbers table called cab_eticket_used so that the number does not come up in the drop down list again.

Can someone please advise how you would do this? I have checked out google and found a few things (such as http://www.artfulsoftware.com/infotree/tip.php?id=799) however dont seem to make much sense, i feel there has to be a better way as i thought this would be a rather common scenario?

Any help would be much appreciated, maybe even if i could use the same query that i use to do the INSERT if i cant have 2 queries.

Any help would be much appreciated.

Thanks very much in advance.

•

Join Date: Aug 2005

Posts: 149

Reputation: sDJh is an unknown quantity at this point

Solved Threads: 13

sDJh

Offline

Junior Poster

Re: Multiple SQL Queries in PHP page

Oct 10th, 2008

Why shouldn't you be able to use more than one query in a php-file?

When I got you problem correctyl you want to save the selected dropdown as a flag in the table

 Help with Code Tags 
 PHP Syntax (Toggle Plain Text) 
mysql_query("UPDATE `cab_eticket_numbers` SET `cab_eticket_used`=1 WHERE `number`=".$yourphpvariable);
mysql_query("UPDATE `cab_eticket_numbers` SET `cab_eticket_used`=1 WHERE `number`=".$yourphpvariable);

and secondly you want to hide it the second time:

 Help with Code Tags 
 PHP Syntax (Toggle Plain Text) 
mysql_query("SELECT *  FROM `cab_eticket_numbers` WHERE `cab_eticket_used`=0");
mysql_query("SELECT *  FROM `cab_eticket_numbers` WHERE `cab_eticket_used`=0");

I hope I got you right.

http://www.explosive-software.de/?lang=en

•

Join Date: Oct 2008

Posts: 5

Reputation: tkmc is an unknown quantity at this point

Solved Threads: 0

tkmc

Offline

Newbie Poster

Re: Multiple SQL Queries in PHP page

Oct 10th, 2008

Yeah kinda what I am after

I don't actually want to save the selected item from the drop down... but the query will be based on one of the drop downs.

Here is a copy of the page. The SQL with variable $sql (The INSERT) works fine and the database is populated perfectly. However the second one with variable $updateused (The UPDATE) does not seem to do anything at all?

 Help with Code Tags 
 php Syntax (Toggle Plain Text) 
<?
include "include_header.php";
include "include_ad.php";
 
echo '<SCRIPT language=JavaScript>';
echo 'function reload(form){';
echo 'var val=form.cab_manager.options[form.cab_manager.options.selectedIndex].value;';
echo "self.location='page_cabticket_submit.php?cab_manager=' + val ;";
echo '}';
echo '</script>';
 
@$cab_manager=$_GET['cab_manager'];
 
$quer2=mysql_query("select distinct cab_manager,cab_manager_id from cab_managernumbers order by cab_manager");
 
if(isset($cab_manager) and strlen($cab_manager) > 0){
$quer=mysql_query("SELECT DISTINCT cab_eticket_number FROM cab_managernumbers where cab_manager_id=$cab_manager and cab_ticket_used=0 order by cab_eticket_number"); 
} 
else{
$quer=mysql_query("SELECT DISTINCT cab_eticket_number FROM cab_managernumbers where cab_ticket_used=0 order by cab_eticket_number"); 
} 
 
echo "<h2>e-Ticket System</h2>\n";
 
if (isset($_POST["submit"])) {
  $date_in_form = explode("-", $_POST["cab_dateused"]);
  //Validate the date
  $error_msg = "";
  if ((intval($date_in_form[0]) < 1) OR (intval($date_in_form[0]) > 31)){
    $error_msg = "Day must be between 1 and 31.";
  } else {}
  if ((intval($date_in_form[1]) < 1) OR (intval($date_in_form[1]) > 12)){
    $error_msg = "Month must be between 1 and 12.";
  } else {}
  if ((intval($date_in_form[2]) < 2000) OR (intval($date_in_form[2]) > 2020)){
    $error_msg = "Year must be between 2000 and 2020.";
  } else {}
} else {}
 
if (isset($_POST["submit"]) AND ($error_msg == "")){
  $form_day = str_pad($date_in_form[0], 2, "0", STR_PAD_LEFT);
  $form_mnt = str_pad($date_in_form[1], 2, "0", STR_PAD_LEFT);
  $new_date = $date_in_form[2] . "/" . $form_mnt . "/" . $form_day;
  $sql  = "INSERT INTO cab_detail (cab_eticket_number, ";
  $sql .= "cab_date_travelled, ";
  $sql .= "cab_location_to, ";
  $sql .= "cab_location_from, ";
  $sql .= "cab_trip_reason, ";
  $sql .= "cab_manager, ";
  $sql .= "cab_issued_to) VALUES (";
  $sql .= "'" . $_POST["cab_eticket_number"] . "', ";
  $sql .= "'" . $new_date . "', ";
  $sql .= "'" . $_POST["cab_locationto"] . "', ";
  $sql .= "'" . $_POST["cab_locationfrom"] . "', ";
  $sql .= "'" . $_POST["cab_reason"] . "', ";
  $sql .= "'" . $_POST["cab_manager"] . "', ";
  $sql .= "'" . $_POST["cab_issueto"] . "')";
 
  $result = mysql_query($sql);
 
  $updateused = "UPDATE cab_managernumbers SET cab_ticket_used = 0 where cab_eticket_number = $cab_eticket_number";
  $resultused = mysql_query($updateused);
 
  echo "Thank you, the ticket has been saved with the below details.<br /><br />";
  echo "Manager Responsible: " . $_POST["cab_manager"] . "<br />";
  echo "e_Ticket Number:     " . $_POST["cab_eticket_number"] . "<br />";
  echo "Issued To:           " . $_POST["cab_issueto"] . "<br />";
  echo "Location To:         " . $_POST["cab_locationto"] . "<br />";
  echo "Location From:       " . $_POST["cab_locationfrom"] . "<br />";
  echo "Date Travelled:      " . $_POST["cab_dateused"] . "<br />";
  echo "Trip Reason:         " . $_POST["cab_reason"] . "<br />";
} else {
 
  for ($i=0; $i<$entries["count"]; $i++)
  {
    if ($entries[$i]["samaccountname"][0] == $user_name) {
      $user_dept = $entries[$i]["department"][0];
      $user_phone = $entries[$i]["telephonenumber"][0];
      $user_email = $entries[$i]["mail"][0];
      $user_full_name  = $entries[$i]["displayname"][0];
    } else {}
  }
 
  if ($error_msg <> ""){
    echo "<table cellspacing=\"0\" cellpadding=\"0\">\n";
    echo "  <tr>\n";
    echo "    <td><font color=\"red\">$error_msg</font><br />Please click the 'back' button and correct the error.</td>\n";
    echo "  </tr>\n";
    echo "</table>\n";
  } else {
    echo "<form action=\"" . $_SERVER["PHP_SELF"] . "\" method=\"post\">\n";
    echo "<table cellpadding=\"2\" cellspacing=\"0\" border=\"0\" width=\"600\" style=\"border: 1px solid #0a246a;\">";
    echo "<tr><td colspan=\"2\">Use this form to submit an e-Ticket entry into the database.<br />\n";
    echo "Click the &quot;Submit&quot; button to save the e-Ticket. Thank you!<br /></td></tr>\n";
    $bg = change_bg($bg);	
 
	echo "<tr bgcolor=\"$bg\"><td>Manager Responsible</td><td><select name='cab_manager' onchange=\"reload(this.form)\"><option value=''>-</option>";
	while($noticia2 = mysql_fetch_array($quer2)) {
	if($noticia2['cab_manager_id']==@$cab_manager){echo "<option selected value='$noticia2[cab_manager_id]'>$noticia2[cab_manager]</option>"."<BR>";}
	else{echo  "<option value='$noticia2[cab_manager_id]'>$noticia2[cab_manager]</option>";}
	}
	echo "</select></td></tr>\n";
	$bg = change_bg($bg);
 
	echo "<tr bgcolor=\"$bg\"><td>e_Ticket Number</td><td><select name='cab_eticket_number'><option value=''>-</option>";
	while($noticia = mysql_fetch_array($quer)) { 
	echo  "<option value='$noticia[cab_eticket_number]'>$noticia[cab_eticket_number]</option>";
	}
	echo "</select></td></tr>\n";
	$bg = change_bg($bg);
 
 
    echo "<tr bgcolor=\"$bg\"><td>Issued to: </td><td><input type=\"text\" name=\"cab_issueto\"  size=\"30\" /></td></tr>\n";
    $bg = change_bg($bg);
 
    echo "<tr bgcolor=\"$bg\"><td>Location From: </td><td><input type=\"text\" name=\"cab_locationfrom\"  size=\"40\" /></td></tr>\n";
    $bg = change_bg($bg);
 
    echo "<tr bgcolor=\"$bg\"><td>Location To: </td><td><input type=\"text\" name=\"cab_locationto\" size=\"40\" /></td></tr>\n";
    $bg = change_bg($bg);
 
    echo "<tr bgcolor=\"$bg\"><td>Travelling Date: </td><td><input type=\"text\" name=\"cab_dateused\" size=\"12\" /> (dd-mm-yyyy)</td></tr>\n";
    $bg = change_bg($bg);
 
    echo "<tr bgcolor=\"$bg\"><td colspan=\"2\">Reason for trip:<br />\n";
    echo "<textarea rows=\"2\" name=\"cab_reason\" cols=\"90\"></textarea></td></tr>\n";
    $bg = change_bg($bg);
 
    echo "<tr bgcolor=\"$bg\"><td><input type=\"submit\" name=\"submit\" value=\"Submit e-Ticket\" /></td><td>&nbsp;</td></tr>\n";
    echo "</table>\n";
    echo "</form>\n";
  }
}
 
include "include_footer.php";
?>
<?
include "include_header.php";
include "include_ad.php";

echo '<SCRIPT language=JavaScript>';
echo 'function reload(form){';
echo 'var val=form.cab_manager.options[form.cab_manager.options.selectedIndex].value;';
echo "self.location='page_cabticket_submit.php?cab_manager=' + val ;";
echo '}';
echo '</script>';

@$cab_manager=$_GET['cab_manager'];

$quer2=mysql_query("select distinct cab_manager,cab_manager_id from cab_managernumbers order by cab_manager");

if(isset($cab_manager) and strlen($cab_manager) > 0){
$quer=mysql_query("SELECT DISTINCT cab_eticket_number FROM cab_managernumbers where cab_manager_id=$cab_manager and cab_ticket_used=0 order by cab_eticket_number"); 
} 
else{
$quer=mysql_query("SELECT DISTINCT cab_eticket_number FROM cab_managernumbers where cab_ticket_used=0 order by cab_eticket_number"); 
} 

echo "<h2>e-Ticket System</h2>\n";

if (isset($_POST["submit"])) {
  $date_in_form = explode("-", $_POST["cab_dateused"]);
  //Validate the date
  $error_msg = "";
  if ((intval($date_in_form[0]) < 1) OR (intval($date_in_form[0]) > 31)){
    $error_msg = "Day must be between 1 and 31.";
  } else {}
  if ((intval($date_in_form[1]) < 1) OR (intval($date_in_form[1]) > 12)){
    $error_msg = "Month must be between 1 and 12.";
  } else {}
  if ((intval($date_in_form[2]) < 2000) OR (intval($date_in_form[2]) > 2020)){
    $error_msg = "Year must be between 2000 and 2020.";
  } else {}
} else {}

if (isset($_POST["submit"]) AND ($error_msg == "")){
  $form_day = str_pad($date_in_form[0], 2, "0", STR_PAD_LEFT);
  $form_mnt = str_pad($date_in_form[1], 2, "0", STR_PAD_LEFT);
  $new_date = $date_in_form[2] . "/" . $form_mnt . "/" . $form_day;
  $sql  = "INSERT INTO cab_detail (cab_eticket_number, ";
  $sql .= "cab_date_travelled, ";
  $sql .= "cab_location_to, ";
  $sql .= "cab_location_from, ";
  $sql .= "cab_trip_reason, ";
  $sql .= "cab_manager, ";
  $sql .= "cab_issued_to) VALUES (";
  $sql .= "'" . $_POST["cab_eticket_number"] . "', ";
  $sql .= "'" . $new_date . "', ";
  $sql .= "'" . $_POST["cab_locationto"] . "', ";
  $sql .= "'" . $_POST["cab_locationfrom"] . "', ";
  $sql .= "'" . $_POST["cab_reason"] . "', ";
  $sql .= "'" . $_POST["cab_manager"] . "', ";
  $sql .= "'" . $_POST["cab_issueto"] . "')";

  $result = mysql_query($sql);
  
  $updateused = "UPDATE cab_managernumbers SET cab_ticket_used = 0 where cab_eticket_number = $cab_eticket_number";
  $resultused = mysql_query($updateused);

  echo "Thank you, the ticket has been saved with the below details.<br /><br />";
  echo "Manager Responsible: " . $_POST["cab_manager"] . "<br />";
  echo "e_Ticket Number:     " . $_POST["cab_eticket_number"] . "<br />";
  echo "Issued To:           " . $_POST["cab_issueto"] . "<br />";
  echo "Location To:         " . $_POST["cab_locationto"] . "<br />";
  echo "Location From:       " . $_POST["cab_locationfrom"] . "<br />";
  echo "Date Travelled:      " . $_POST["cab_dateused"] . "<br />";
  echo "Trip Reason:         " . $_POST["cab_reason"] . "<br />";
} else {

  for ($i=0; $i<$entries["count"]; $i++)
  {
    if ($entries[$i]["samaccountname"][0] == $user_name) {
      $user_dept = $entries[$i]["department"][0];
      $user_phone = $entries[$i]["telephonenumber"][0];
      $user_email = $entries[$i]["mail"][0];
      $user_full_name  = $entries[$i]["displayname"][0];
    } else {}
  }

  if ($error_msg <> ""){
    echo "<table cellspacing=\"0\" cellpadding=\"0\">\n";
    echo "  <tr>\n";
    echo "    <td><font color=\"red\">$error_msg</font><br />Please click the 'back' button and correct the error.</td>\n";
    echo "  </tr>\n";
    echo "</table>\n";
  } else {
    echo "<form action=\"" . $_SERVER["PHP_SELF"] . "\" method=\"post\">\n";
    echo "<table cellpadding=\"2\" cellspacing=\"0\" border=\"0\" width=\"600\" style=\"border: 1px solid #0a246a;\">";
    echo "<tr><td colspan=\"2\">Use this form to submit an e-Ticket entry into the database.<br />\n";
    echo "Click the &quot;Submit&quot; button to save the e-Ticket. Thank you!<br /></td></tr>\n";
    $bg = change_bg($bg);	
	
	echo "<tr bgcolor=\"$bg\"><td>Manager Responsible</td><td><select name='cab_manager' onchange=\"reload(this.form)\"><option value=''>-</option>";
	while($noticia2 = mysql_fetch_array($quer2)) {
	if($noticia2['cab_manager_id']==@$cab_manager){echo "<option selected value='$noticia2[cab_manager_id]'>$noticia2[cab_manager]</option>"."<BR>";}
	else{echo  "<option value='$noticia2[cab_manager_id]'>$noticia2[cab_manager]</option>";}
	}
	echo "</select></td></tr>\n";
	$bg = change_bg($bg);
	
	echo "<tr bgcolor=\"$bg\"><td>e_Ticket Number</td><td><select name='cab_eticket_number'><option value=''>-</option>";
	while($noticia = mysql_fetch_array($quer)) { 
	echo  "<option value='$noticia[cab_eticket_number]'>$noticia[cab_eticket_number]</option>";
	}
	echo "</select></td></tr>\n";
	$bg = change_bg($bg);
	
	
    echo "<tr bgcolor=\"$bg\"><td>Issued to: </td><td><input type=\"text\" name=\"cab_issueto\"  size=\"30\" /></td></tr>\n";
    $bg = change_bg($bg);
	
    echo "<tr bgcolor=\"$bg\"><td>Location From: </td><td><input type=\"text\" name=\"cab_locationfrom\"  size=\"40\" /></td></tr>\n";
    $bg = change_bg($bg);
	
    echo "<tr bgcolor=\"$bg\"><td>Location To: </td><td><input type=\"text\" name=\"cab_locationto\" size=\"40\" /></td></tr>\n";
    $bg = change_bg($bg);
	
    echo "<tr bgcolor=\"$bg\"><td>Travelling Date: </td><td><input type=\"text\" name=\"cab_dateused\" size=\"12\" /> (dd-mm-yyyy)</td></tr>\n";
    $bg = change_bg($bg);
	
    echo "<tr bgcolor=\"$bg\"><td colspan=\"2\">Reason for trip:<br />\n";
    echo "<textarea rows=\"2\" name=\"cab_reason\" cols=\"90\"></textarea></td></tr>\n";
    $bg = change_bg($bg);
	
    echo "<tr bgcolor=\"$bg\"><td><input type=\"submit\" name=\"submit\" value=\"Submit e-Ticket\" /></td><td>&nbsp;</td></tr>\n";
    echo "</table>\n";
    echo "</form>\n";
  }
}

include "include_footer.php";
?>

Last edited by tkmc; Oct 10th, 2008 at 9:24 am.

•

Join Date: Oct 2008

Posts: 5

Reputation: tkmc is an unknown quantity at this point

Solved Threads: 0

tkmc

Offline

Newbie Poster

Re: Multiple SQL Queries in PHP page

Oct 10th, 2008

I have not yet solved the problem as i still can not get the UPDATE query to use the variable $cab_eticket_number to be used as part of the SQL query.

I did spot an error in my above code with the UPDATE query where it was setting it to 0 instead of 1, this has been corrected, however as i said still having the issue.

I tried to hard code the UPDATE without using the $cab_ticket_number variable and it seemed to work ok.... anyone have any ideas what im doing wrong here?

Thanks

•

Join Date: Oct 2008

Posts: 5

Reputation: tkmc is an unknown quantity at this point

Solved Threads: 0

tkmc

Offline

Newbie Poster

Re: Multiple SQL Queries in PHP page

Oct 10th, 2008

Spotted the problem, instead of using the variable name i should have used the POST to get the ticket number. I replace the UPDATE with the one below and all works perfect now.

 Help with Code Tags 
 php Syntax (Toggle Plain Text) 
$updateused = "UPDATE cab_managernumbers SET cab_ticket_used = 1 where cab_eticket_number = ". $_POST["cab_eticket_number"] ."";
$updateused = "UPDATE cab_managernumbers SET cab_ticket_used = 1 where cab_eticket_number = ". $_POST["cab_eticket_number"] ."";

•

Join Date: May 2008

Posts: 524

Reputation: Will Gresham is on a distinguished road

Solved Threads: 86

Will Gresham Will Gresham is offline

Offline

Posting Pro

Re: Multiple SQL Queries in PHP page

Oct 10th, 2008

•

Originally Posted by tkmc

Spotted the problem, instead of using the variable name i should have used the POST to get the ticket number. I replace the UPDATE with the one below and all works perfect now.
Help with Code Tags

php Syntax (Toggle Plain Text)
$updateused = "UPDATE cab_managernumbers SET cab_ticket_used = 1 where cab_eticket_number = ". $_POST["cab_eticket_number"] ."";
$updateused = "UPDATE cab_managernumbers SET cab_ticket_used = 1 where cab_eticket_number = ". $_POST["cab_eticket_number"] ."";

This may work but is not recommended and certainly isn't secure.

Taking variables directly from the POST, GET or QUERY in PHP is not safe, it leaves your script vunerable to injection attacks.. You should be taking the POST variable and sanitizing it first (for instance, a number should only contain the characters 0-9) you could do the following:

 Help with Code Tags 
 php Syntax (Toggle Plain Text) 
if(is_numeric($_POST["cab_eticket_number"])) {
$cab_eticket_number = $_POST["cab_eticket_number"];
} else {
  //Process an input which is nit numeric.
}
$updateused = "UPDATE cab_managernumbers SET cab_ticket_used = 1 where cab_eticket_number = $cab_eticket_number";
if(is_numeric($_POST["cab_eticket_number"])) {
$cab_eticket_number = $_POST["cab_eticket_number"];
} else {
  //Process an input which is nit numeric.
}
$updateused = "UPDATE cab_managernumbers SET cab_ticket_used = 1 where cab_eticket_number = $cab_eticket_number";

Last edited by Will Gresham; Oct 10th, 2008 at 12:04 pm.

AJAX is not a programming language, scripting language or any other sort of language.
It is acheived by using JavaScript http functions.
So, AJAX = JavaScript.

•

Join Date: Sep 2007

Posts: 54

Reputation: HazardTW is an unknown quantity at this point

Solved Threads: 2

HazardTW

Offline

Junior Poster in Training

Re: Multiple SQL Queries in PHP page

Oct 11th, 2008

You beat me to it Xan

I too noticed the use of $_POST and $_GET vars directly in the queries, something you absolutely do not want to do.

If you have some kind of user-input validation on the client-side, it isn't good enough, client-side validation is for user convenience so they don't have to post the page only to have it reload and tell them there was a problem.

This is one lesson you do not want to learn the hard way