Multiple SQL Queries in PHP page

[tkmc]

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.

[sDJh]

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

mysql_query("UPDATE `cab_eticket_numbers` SET `cab_eticket_used`=1 WHERE `number`=".$yourphpvariable);

and secondly you want to hide it the second time:

mysql_query("SELECT *  FROM `cab_eticket_numbers` WHERE `cab_eticket_used`=0");

I hope I got you right.

[tkmc]

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?

<?
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";
?>

[tkmc]

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

[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.

$updateused = "UPDATE cab_managernumbers SET cab_ticket_used = 1 where cab_eticket_number = ". $_POST["cab_eticket_number"] ."";

[Will Gresham]

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.

$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:

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";

[HazardTW]

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 ;)