PHP - Moved: Multiple Count(*) Queries In One Query ?

This topic has been moved to MySQL Help.

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

Hi all,

I've built a few amateur sites in the past but am keen to utilize php combined with mysql for my current project.  However, I have little knowledge of php and am just picking it up as I go (same way I've done for all my computer knowledge)

What I'm looking for may sound simple, but I really don't know where to start and I'm hoping there are some kind souls here that will lend a hand.  I've got some fantastic help here before and am hoping I'll get lucky again.

SQL Database
meetingid venue
001 new york
002 chicago
003 new york
004 new york
005 new york
006 texas
007 texas
008 chicago
009 new york
010 new york

Question
If I hard code everything around it, I'm just looking for the code I would need to have it display as follows: (hard coded text in blue)

"New York has been used * times." 
(Where * would count the number of times "New York" appears in the venue column of the database.)
"The most consecutive times New York has been used is % ." 
(Where % would count the maximum number of consecutive times "New York" appears in the venue column of the database.)

Giving the output:
New York has been used 6 times.
The most consecutive times New York has been used is 3.

This topic has been moved to MySQL Help.

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

This topic has been moved to MySQL Help.

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

This topic has been moved to MySQL Help.

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

The whole code is below, followed by the part that isn't producing.  It all works extremely well until the last part, and I have two instances of setting a variable to mysql_fetch_assoc that work.  Not sure why the third attempt doesn't.  I'm just trying to have a separate area that outputs 'msg'.

Code: [Select]

$sql = "SELECT * FROM players as p
INNER JOIN schools as s
WHERE p.tid = s.id
ORDER BY status, playerLast";

$results = mysql_query($sql);



echo '<div class="roster">';

$team = mysql_fetch_assoc($results);

echo '<div class="team_info">
<div class="school">' . $team['school'] . '</div>
<div class="coach">Coach ' .$team['coachFirst'] . ' ' . $team['coachLast'] .'</div>
<div>Sectional: ' . $team['sectional'] . '</div>
<div>Class: ' . $team['class'] . 'A</div>
';

echo '</div>';


  $currentStatus = false;  //Flag to detect change in status
    while($player = mysql_fetch_assoc($results))
    {


        if($currentStatus != $player['status'])
        {
            //Status has changed, display status header
            $currentStatus = $player['status'];
echo '<br><b>';
if ($currentStatus == '1')  {echo 'Returning Starters';}
elseif ($currentStatus == '2')  {echo 'Key Returners';}
elseif ($currentStatus == '3')  {echo 'Varsity Newcomers';}
elseif ($currentStatus == '4')  {echo 'Key Freshmen';}
echo '</b><br>';
           // echo "<div><b>{$currentStatus}</b></div>\n";
        }
        //Display player info
        echo $player['playerFirst'] . ' ' . $player['playerLast'] . ', ' . $player['feet'] . '\'' . $player['inches'] . '",' . $player['position'] . ', ' . $player['year'] . ';<br>';
 
    }

        //Player comments
echo '<hr>';
echo '<div class="coach_comments"><span class="player_name">hello';

while($comments = mysql_fetch_assoc($results)) {


echo $comments['playerFisrt'] . ' ' . $comments['playerLast'] . ':  </span>' . $comments['msg'];
}
echo '</div>';
echo '</div>';



It's all working until what's below.  All I get out of it is the "hello", which is just a test to make sure it wasn't a CSS issue.

Quote


        //Player comments
   echo '<hr>';
   echo '<div class="coach_comments"><span class="player_name">hello';
   
   while($comments = mysql_fetch_assoc($results)) {

      
      echo $comments['playerFisrt'] . ' ' . $comments['playerLast'] . ':  </span>' . $comments['msg'];
   }
echo '</div>';
echo '</div>';

Hi everyone

I have the following PHP code

$sql.= "INSERT INTO `data` (`info`, `write`, `date`) VALUES";
$sql.= "(";
        for ($c=0; $c < $num; $c++) {
$sql.= '"'.str_replace('"', "", $data[$c]).'",';
        }
$sql.= "'".date('Y-m-d')."');";

If I

print $sql;

I get

Code: [Select]
INSERT INTO `data` (`info`, `write`, `date`) VALUES("data99","n",'2010-10-05');
INSERT INTO `data` (`info`, `write`, `date`) VALUES("data101","y",'2010-10-05');
INSERT INTO `data` (`info`, `write`, `date`) VALUES("data876","n",'2010-10-05');
what would I need to do to my PHP code in order for it to process each QUERY either all at once or one at a time

Whatever I try, it either inserts nothing, or just does the first INSERT

Any ideas?

Thanks

I am using two seperate queries to calculate a streak, but the queries must be grouped together to find the actual streak?

Query 1:

SELECT COUNT(matchID) as streak, clan1 FROM webs_cup_matches WHERE ladID='17' AND clan1='2630' AND score1 > score2 AND confirmscore='1' AND einspruch='0' GROUP BY clan1 ORDER BY streak DESC LIMIT 1
Query 2:
SELECT COUNT(matchID) as streak, clan2 FROM webs_cup_matches WHERE ladID='17' AND clan2='2630' AND score1 < score2 AND confirmscore='1' AND einspruch='0' GROUP BY clan2 ORDER BY streak DESC LIMIT 1

is it possible someone can join these queries together?

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

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

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

?>

This topic has been moved to MySQL Help.

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

This topic has been moved to MySQL Help.

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

This topic has been moved to MySQL Help.

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