PHP - Multiple Queries In One Query & Php

Not sure where to begin describing ths one?!   

On my website I have Articles.  Beneath each Article, Users can leave one or more Comments.

Next to each User's Comment, I have the following in the left margin...
- Username
- User Online Status
- User Photo
- User Location
- User # of Posts


At the top of my file, I have my PHP code and I start off by running a Prepared Statement looking for the Article the User is requesting in the URL.

If the Article is found, I store the Results, and then run another Prepared Statement looking for Comments. 

If Comments are found, I store the Results.

Then down in the HTML section, I display the Article and beneath it I loop through the Article's Comments like this...
Code: [Select]
// ********************************
// Display Comments on Article. *
// ********************************
while (mysqli_stmt_fetch($stmt2)){
// Display User Info. *

// Display User Comments. *
}

This seems to be working nicely, except that there is one last piece of User Info that is messing things up...

I can get all of the User and Comments data in this query...
Code: [Select]
// ************************
// Build Comments Query. *
// ************************

// Build query.
$q2 = 'SELECT m.first_name, m.username, m.photo_name, m.photo_label,
m.location, m.created_on, m.logged_in, m.last_activity,
c.created_on, c.body, c.status
FROM member AS m
INNER JOIN comment AS c
ON m.id = c.member_id
WHERE c.status="Approved" AND c.article_id=?
ORDER BY c.created_on';

However, in order to get the User's "Number of Posts" I need another query.  And I'm not sure how to get the "Post Count" for each User and then mesh it back into what I currently have?!

Does that make sense?!

Also, it is conceivable that later I might add additional User Info that comes from yet another Query/Source.  (If I was using OOP and PDO and had a clear separation of "Presentation" from "Business Logic" I'm sure would be easier, but for now I need to work with the structure I have since any of those three things are way beyond the scope of this question and my current abilities!!!!)

Thanks,


Debbie

I'm working on a Stats page that will need to run multiple queries to access the information needed to dislpay on the page. Rather than running 30+ queries to get the user's Stats, is there a better method?
Any tips that you suggest I further research?

Thanks

Hi all,

I would like to perform two queries in a mysqli statement before closing the connection. I'm using prepared statements and I've read on the php.net you can perform a multi-query using mysqli->multi_query.
My problem is that I'm only finding useful examples on how to select and fetch results not using prepared statements. I'm a bit lost with how to do this to update two tables. Currently i'm using two seperate connections to do the queries but want to combine these together:

Query one (updates their password and resets number of logins to force password change on next login):

Code: [Select]
                //reset logins to force the user to change password upon next login
$loggedin = '0';

// connect to db for mysqli
require('../db/db.php');

// updates password and number of logins
$insert_stmt = $mysqli->stmt_init();
if ($insert_stmt->prepare("UPDATE users SET their_password=?, loggedin=? WHERE their_username=?")) {
$insert_stmt->bind_param('sss', $their_password, $time, $user_email);
$insert_stmt->execute();
$insert_stmt->close();
}
// if query errors sends an email
if ($mysqli->error) {
try {   
throw new Exception("MySQL error $mysqli->error <br> Query:<br> $query", $mysqli->errno);   
} catch(Exception $e ) {
$mess = "Error No: ".$e->getCode(). " - ". $e->getMessage() . "<br >";
$mess .= nl2br($e->getTraceAsString());
$contact_email = "webmaster@website.com";
$message_sub = "Mysqli Forgotten Password Query Error [UPASSWORD01]";
$hdrs = "From: " . $contact_email . "\r\n";
$hdrs .= "Reply-To: ". $contact_email . "\r\n";
$hdrs .= "MIME-Version: 1.0\r\n";
$hdrs .= "Content-Type: text/html; charset=UTF-8\r\n";
mail($contact_email, $message_sub, $mess, $hdrs);
}
header("refresh: 10; forgotpass.php");
die('ERROR: Unable to reset password. Please check you details and try again or report this error to us using our contact us form.<br><br>We will redirect you back to the forgotten password form in 10 seconds.');
exit();
}
$mysqli->close();

Query two (updates a reset log - to keep track on our user password resets):

Code: [Select]
// change status
$resetstatus = "Successful Reset";

// connect to db for mysqli
require('../db/db.php');

// inserts a new record
$null = NULL;
$insert_stmt = $mysqli->stmt_init();
if ($insert_stmt->prepare("INSERT INTO passwordresets VALUES (?, ?, ?, ?, ?)")) {
$insert_stmt->bind_param('issss', $null, $user_email, $time, $userip, $resetstatus);
$insert_stmt->execute();
$insert_stmt->close();
}

// if query errors sends an email
if ($mysqli->error) {
try {   
throw new Exception("MySQL error $mysqli->error <br> Query:<br> $query", $mysqli->errno);   
} catch(Exception $e ) {
$mess = "Error No: ".$e->getCode(). " - ". $e->getMessage() . "<br >";
$mess .= nl2br($e->getTraceAsString());
$contact_email = "webmaster@website.com";
$message_sub = "Mysqli Forgotten Password Query Error [UARESETLOG01]";
$hdrs = "From: " . $contact_email . "\r\n";
$hdrs .= "Reply-To: ". $contact_email . "\r\n";
$hdrs .= "MIME-Version: 1.0\r\n";
$hdrs .= "Content-Type: text/html; charset=UTF-8\r\n";
mail($contact_email, $message_sub, $mess, $hdrs);
}
header("refresh: 10; forgotpass.php");
die('ERROR: Unable to reset password. Please check you details and try again or report this error to us using our contact us form.<br><br>We will redirect you back to the forgotten password form in 10 seconds.');
exit();
}
$mysqli->close();

Any ideas would be greatly appreciated. Thank you

Main script gets class information from a database and prints them so long as the class start date or end date is after today (actually includes today). Main script calls "instructors.php".  It queries another database based on the instructor name ($chef) and then prints the bio information for that instructor.   "instructors.php" works fine on it's own, when I add "$chef = "Chef Name" ("Chef Name" is in the Instructors database).  When it's called from the main script, nothing shows up in that area - even though "Chef Name" is in the database.  All of the other data is printed fine, just not anything from instructors.php.  I verified that it's actually including the file, as I can add "echo "test";" to the top of instructors.php and it prints fine in the main script.   Any ideas of what I'm missing?       Main Script 

<?php 

// Get required login info
include "/path/to/login/info/file.php"; // Get required login info - changed for this post.

$db = new mysqli('localhost', $username, $password, $database); // Connect to DB using required login info
if($db->connect_errno > 0){
    die('Unable to connect to database [' . $db->connect_error . ']');
}

unset($username);// put these variables back to null
unset($password);// put these variables back to null
unset($database);// put these variables back to null


//query db
$sql = <<<SQL
    SELECT *
    FROM `ft_form_7`
	WHERE DATE(class_start_date) >= CURDATE()
	OR DATE(class_end_date) >= CURDATE()
	ORDER BY class_start_date ASC
SQL;

if(!$result = $db->query($sql)){ // if there is an error in running the query, show error message.
    die('There was an error running the query [' . $db->error . ']');
}

while($row = $result->fetch_assoc()){
	// Get start date information
	$start_date = $row['class_start_date']; // Get event_start_date for conversion and call it $start_date
	$start_date_formatted = date("l M d, Y", strtotime($start_date)); // Convert start_date 
	$end_date = $row['class_end_date']; // Get event_end_date for conversion and call it $start_date
	$end_date_formatted = date("M d, Y", strtotime($end_date)); // Convert start_date
	
	// Get time information.
	$start_time = $row['class_start_time']; // Get event_start_time for conversion and call it $start_time
	$start_time_formatted = date("h:i A", strtotime($start_time)); // Convert start_time 
	$end_time = $row['class_end_time']; // Get event_end_time for conversion and call it $end_time
	$end_time_formatted = date("h:i A", strtotime($end_time)); // Convert end_time 
	
	// echo information...
	    echo "<h2>" , $row['class_name'],"</h2>" ; // echo event name
	    echo "<p><strong>",$start_date_formatted; // echo the start date
		if (empty($start_time)) { echo ''; } else { echo " (", $start_time; } // echo start time
		if (empty($end_date)) { echo ''; } else { echo " -","<br />", $end_date_formatted; } // echo end date
		if (empty($end_time)) { echo ')'; } else { echo " - ", $end_time, ")"; } // echo end time
	// if there is no start time, echo nothing. (otherwise it seems to echo 4pm). If it does contain a time, echo the time.
	echo "</strong><br />";

	$chef = $row['Instructor'];
	global $chef;
	
	if ($chef != NULL) {
		require ('instructors.php'); }
	echo $row['class_description'], "<br />";
	echo "<strong>" , $row['type'], "&nbsp;-&nbsp;Cost: &#36;",$row['cost'] , " - #" , $row['course_number'] , "</strong><br />" , "</p>"; // echo class type and cost
}

$db->close();
$result->free();

?>

 instructors.php

<?php

include "/path/to/login/info/file.php"; // Get required login info - changed for this post.

$db_instructors = new mysqli('localhost', $username, $password, $database); // Connect to DB using required login info
if($db_instructors->connect_errno > 0){
    die('Unable to connect to database [' . $db_instructors->connect_error . ']');
}

unset($username);// put these variables back to null
unset($password);// put these variables back to null
unset($database);// put these variables back to null

//query db
$sql_instructors = <<<SQL
    SELECT *
    FROM ft_form_8
    WHERE chef_name = '$chef'
SQL;

if(!$result_instructors = $db_instructors->query($sql_instructors)) { // if there is an error in running the query, show error message.
    die('There was an error running the query [' . $db_instructors->error . ']');
}

	while($row_instructors = $result_instructors->fetch_assoc()) {
	$chef_full_name = $row_instructors['chef_name'];
	$chef_id = $row_instructors['submission_id'];
	$full_bio = $row_instructors['full_bio'];
	echo "<a href=\"#\" class=\"clickme\">" , $chef_full_name , "</a>";
	echo "<div class=\"box\">";
	echo $full_bio , "</div>";
}


$db_instructors->close();
$result_instructors->free();

?>

I'm using paypal PDT, so when the user has made payment he gets directed back to my site, on that page it executes a special sql query. I noticed I can refresh the page to make duplicate sql queries. How can I avoid this??

This topic has been moved to MySQL Help.

http://www.phpfreaks.com/forums/index.php?topic=351561.0

these DB  are on the same server
sp
inter
query
how do i combine the mysql_db()?
Code: [Select]
SELECT * FROM `sp.bookings` LEFT JOIN sp.bookings_items ON sp.bookings.id_item = sp.bookings_items.id LEFT JOIN inter.apartments ON inter.apartments.ID = sp.bookings_items.id_ref_external WHERE  sp.bookings.the_date BETWEEN '$from_date' and '$to_date' and id_state=1   LIMIT 0 , 30

Hi there,

This is what I need to do, blow that is the code I have knocked up to do it but it doesn't seem to work. The main problem is the query that matches the atc callsigns to the pilots destinations.. can't fathom it myself.

1. Pull all the records from my database.
2. Assign their unique user id's, callsigns and destinations.
3. Execute a query in which you: SELECT * FROM CLIENTS WHERE (The 'clienttype' = 'ATC') AND match the callsign of the ATC users to the 'planned_destairport' of the 'clienttype' = 'PILOT'.

For example if three people are going to Heathrow, then it would return 3 results.
4. Then do a mysql_num_rows of the result.
5. Lastly, execute a query that would update the 'atcarrivals' collumn in the database with the count number using the unique 'cid' as the identifier.

All in a loop of course.


// Selects all of the ATC Records.
$atc_arrivals_selectquery = mysql_query ("SELECT * FROM CLIENTS");

// Starts the loop for fetching the records of the previous query.
while($atc_arrivals_row = mysql_fetch_array($atc_arrivals_selectquery)){

// Sets the users cid, planned_destairport and callsign.
$atc_cid = $atc_arrivals_row['cid'];
$atc_destinations = $atc_arrivals_row['planned_destaiport'];
$atc_callysign = $atc_arrivals_row['callsign'];

// Searches the database and selects all records where the callsign is LIKE the destination airport.
$atc_arrivals_check = mysql_query("SELECT * FROM CLIENTS WHERE clienttype = 'ATC' AND callsign LIKE '%$atc_destinations'");

// Counts previous query results.
$atc_arrivals_count = mysql_num_rows($atc_arrivals_check);

// Updates the atcarrivals column with the count..
$atc_arrivals_updatequery = mysql_query("UPDATE CLIENTS SET atcarrivals = '$atc_arrivals_count' WHERE cid = '$atc_cid' AND clienttype = 'ATC'");

// End loop.
}


Concerned columns are these:
"cid" - Unique client id.
"atcarrivals" - Number of arrivals from count. (This is the problem.)
"planned_destairport" - Destination airport of pilot. Usually four letters long. IE. EGLL = Heathrow.

Thanks.

Hello   We have a database table that confirms the installations started and completed for our game... and I am looking to confirm how many installs start but never complete...   So, this is my current query...

SELECT description, ip from error_log where description like '%install%' order by ip;

 

What I am trying to do is create a search box to search for a specific piece of information in my client database, say last name, or account number, invoice number, etc. is there a way i can use the keyword to match the query to multiple columns?

here is my code, the script works if i use the $query to select just 1 column. but how do i add other columns to match it to?

I know its  where "different columns" LIKE search  my problem is this line basically.

Code: [Select]
mysql_select_db("terra_elegante_operations", $con);



$searchfor = $_GET['search_term'];


$query = "select * from client_information where name_last like \"%$searchfor%\"";

$result = mysql_query($query) or die("Couldn't execute query");

$row = mysql_fetch_array($result);

echo $row['0'];

Hello,

I'm fairly new to php and I'm working on a website for a chess club. I'd like the user to select all of his or her personal results from a general results table.  The results table looks as follows:

id         name_white         name_black         result           season_id

1          john                    pete                    1-0              2010-2011
2          charles                steve                   0-1              2010-2011
3          richard                john                    1-0               2010-2011
etc
11        isaac                   john                    1-0               2011-2012
12        william                kate                     0-1              2011-2012
13        john                    bobby                  1-0              2011-2012
etc

Let us say that the user wants to see all the results of player john in the 2010-2011 season.  The following query (probably quite naively) gives me the error "supplied argument is not a valid MySQL result resource":

Code: [Select]
$result_personal = mysql_query("SELECT * FROM results WHERE name_white = '$seleted_name' AND name_black = '$selected_name' AND season id ='$selected_season'");
Could anyone help me steer in the right direction?  Should I use a JOIN query instead?  Or a IN query?

All the best and thanks in advance for your time,

Wouter.

Hi guys,
I need your help!
I have two tables "sp_users" and "sp_schools" in the same database.
Now I want to add column "time" from "sp_schools" to a phpfile which displays "sp_users".
The column "time" should be after column "update".

I made it upto here, but to get the second query and display
Please help. I am devastated...

Code: [Select]
<?php
$host="xxx"; // Host name
$username="xxx"; // Mysql username
$password="xxx"; // Mysql password
$db_name="xxx"; // Database name
$tbl_name="sp_users"; // Table name

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

$sql="SELECT * FROM $tbl_name ORDER BY user_id";
$result=mysql_query($sql);
?>
<style type="text/css">
<!--
.style2 {font-weight: bold}
.style3 {
font-family: Arial, Helvetica, sans-serif;
color: #000033;
}
.style8 {font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 10px; color: #003333; }
-->
</style>

<title>User overview</title><table width="486" border="0" align="center" cellpadding="0" cellspacing="1">
<tr>
<td width="427">
 
    <div align="left">
      <table width="486" border="1" cellspacing="0" cellpadding="3">
        <tr>
          <td colspan="4"><div align="center" class="style1 style3"><strong>SchoolPorta Users </strong></div></td>
        </tr>
        <tr>
          <td width="26" align="center"><span class="style2">id</span></td>
          <td width="70" align="center"><span class="style2">Name</span></td>
          <td width="114" align="center"><span class="style2">Lastname</span></td>
          <td width="146" align="center"><span class="style2">Email</span></td>
          <td width="88" align="center"><span class="style2">Update</span></td>
        </tr>
        <?php
while($rows=mysql_fetch_array($result)){
?>
        <tr>
          <td><span class="style8"><? echo $rows['user_id']; ?></span></td>
          <td><span class="style8"><? echo $rows['user_first_name']; ?></span></td>
          <td><span class="style8"><? echo $rows['user_surname']; ?></span></td>
          <td><span class="style8"><? echo $rows['user_login']; ?></span></td>
          <td align="center"><a href="update.php?id=<? echo $rows['user_id']; ?>" class="style8">update</a></td>
        </tr>
        <?php
}
?>
      </table>
    </div></td>
</tr>
</table>
<div align="center">
  <?php
mysql_close();
?>
</div>

I can use the first while loop, but there is no data in the second while loop. Is there a better way as I have never done this before...

Code: [Select]
              <?php
                $featured_results = mysql_query("SELECT * FROM products LEFT JOIN product_images ON products.product_id=product_images.product_id WHERE products.product_featured='1' AND products.product_active='1' AND thumb='1'");

$fa=0;
while($featured_row = mysql_fetch_assoc($featured_results))
   {
$fthumb_result = mysql_query("SELECT image_name FROM product_images WHERE product_id='".$featured_row['product_id']."' AND thumb='1'");
$fthumb = mysql_fetch_row($fthumb_result);

if ($fa==0) {
echo "\n<img id=\"home-slider-photo-".$fa."\" class=\"home-slider-photo preload\" src=\"/includes/getimage.php?img=".$fthumb[0]."&w=370&h=370\" alt=\"\" />";
} else {
echo "\n<img id=\"home-slider-photo-".$fa."\" class=\"home-slider-photo preload home-slider-photo-unsel\" src=\"/includes/getimage.php?img=".$fthumb[0]."&w=370&h=370\" alt=\"\" />";
}
$fa++;
}

echo "<div id=\"home-slider-photo-price\">";
$fb=0;
while($featured_row2 = mysql_fetch_assoc($featured_results))
   {
if ($fb==0) {
echo "\n<div id=\"home-slider-photo-price-".$fb."\" class=\"home-slider-photo-price\">\n<span>only</span>$".$featured_row2['product_price']."\n</div>";
} else {
echo "\n<div id=\"home-slider-photo-price-".$fb."\" class=\"home-slider-photo-price home-slider-photo-price-unsel\">\n<span>only</span>$".$featured_row2['product_price']."\n</div>";
}
$fb++;
}
echo "</div>";
?>

$query = mysql_query("SELECT COUNT(tickets.id),tickets.id,tickets.status,tickets.date,tickets.question,tickets.title,users.position FROM tickets,users WHERE tickets.id='$existing' users.username='$username'");


How do I get it so it selects data from the table tickets where id='$existing', but I also want to get a user's (who is viewing the ID) position (rank). $username is a session. So, any thoughts on how I would do so?

My updated code:


<?php
session_start();
include("../includes/mysql.php");
include("../includes/config.php");

?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<link href="../style.css" rel="stylesheet" type="text/css" />
<title><?php echo $title; ?></title>
</head>

<body>
<div id="container">
<div id="content">
<div id="left">
<div class="menu">
<?php include("../includes/navigation.php"); ?>
<div class="menufooter"></div>
</div>
<?php include("../includes/menu.php"); ?>
</div>
<div id="middle">
<?php
$existing = $_POST['existing'];
$ip = $_SERVER['REMOTE_ADDR'];
$username = $_SESSION['user'];

$query = mysql_query("SELECT COUNT(tickets.id),tickets.id,tickets.status,tickets.date,tickets.question,tickets.title,users.position FROM tickets,users WHERE tickets.id='$existing' OR users.username='$username'");
$get = mysql_fetch_assoc($query);

if(!$existing)
{
echo 
'
<div class="post">
<div class="postheader"><h1>Error</h1></div>
<div class="postcontent">
<p>You have not enetered in a ticket ID. Please go back and do so.</p>
</div>
<div class="postfooter"></div>
</div>
';
}
elseif($get['COUNT(id)'] < 1)
{
echo 
'
<div class="post">
<div class="postheader"><h1>Error</h1></div>
<div class="postcontent">
<p>The ticket ID you are trying to use doesnt exist. Please go back or submit another ticket.</p>
</div>
<div class="postfooter"></div>
</div>
';
}
elseif($get['tickets.ip']==$ip || $get['user.position'] >= 1)
{
$status["tickets.status"]["0"] = "Waiting for support...";
$status["tickets.status"]["1"] = "Waiting for user...";
$status["tickets.status"]["2"] = "Ticket Closed...";
$status["tickets.status"]["3"] = "Ticket Opened...";

echo 
'
<div class="post">
<div class="postheader"><h1>View Ticket Status - ID '. $get["id"] .'</h1></div>
<div class="postcontent">
<p>Title: '. $get["tickets.title"] .' - Posted on: '. $get["tickets.date"] .'</p>
<p>Ticket Status: '. $status[$get["tickets.status"]] .'</p>
<p>Question: '. nl2br($get["tickets.question"]) .'</p>
</div>
<div class="postfooter"></div>
</div>
';

}
else
{
echo 
'
<div class="post">
<div class="postheader"><h1>Error</h1></div>
<div class="postcontent">
<p>This is not your ticket. You only have permission to view your tickets. Please go back.</p>
</div>
<div class="postfooter"></div>
</div>
';
}
?>
</div>
</div>
</div>
</body>
</html>

According to http://www.phpfreaks.com/forums/index.php?topic=350872.0:

Is this a correct usage of what I am trying to create?

Code: [Select]
<?php

$connect = mysql_connect("mysql12.000webhost.com","***","***") or die(mysql_error());
mysql_select_db("***") or die(mysql_error());


$id = mysql_real_escape_string($_GET['id']);
$extract = mysql_query("SELECT * FROM webclientcreate WHERE id='$id' LIMIT 1 WHERE ip='$ip' WHERE port='$port' WHERE name='$name' WHERE background='$background'");
?> Code: [Select]
<applet name=client width=765 height=503 archive='<?php echo $jars; ?>' code='client.class'>

 <PARAM name='java_arguments' value='-Xmx700m'>

 <PARAM name='server_name' value='<?php echo $name; ?>'>

 <PARAM name='detail' value='low'>

 <PARAM name='ip' value='<?php echo $ip; ?>'>

 <PARAM name='port' value='<?php echo $port; ?>'>

 <PARAM name='background_image' value='<?php echo $background; ?>'>

 <PARAM name='headicons' value=''>

I've tried several types of the query line such as :
Code: [Select]
webclientcreate WHERE id='$id' LIMIT 1, WHERE ip='$ip', WHERE port='$port' Code: [Select]
webclientcreate WHERE id='$id' LIMIT 1, ip='$ip', port='$port' Code: [Select]
webclientcreate WHERE id='$id' LIMIT 1 ip='$ip' port='$port'Without any success 

I appreciate any help I can get!