Some help with 2 queries

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
ianhull
Forum Contributor
Posts: 310
Joined: Tue Jun 14, 2005 10:04 am
Location: Hull England UK

Some help with 2 queries

Post by ianhull »

Hi Guys,

I would like to query the database and select certain users like this.

Code: Select all


$getUsers = mysql_query("SELECT
id,
firstname,
lastname,
location,
level FROM users where level = 'User' AND location = '$location'")or die(mysql_error());

if(mysql_affected_rows() == 0){
//
exit();
};
while ($usersRecieved = mysql_fetch_array($getUsers))    
{
extract($usersRecieved);
//
}


Then for each user that is selected I would like to use thier name as a header and select current appointments from a different table.

Before this second query I have declared a variable called

Code: Select all

$name = $firstname . ' ' . $lastname;

Code: Select all


$checkCalendarDay = mysql_query("SELECT 
id,
name,
company_name,
appointment_time,
appointment_day,
appointment_month,
appointment_year,
city_town,
location,
postcode_1,
postcode_2 FROM currentappointments WHERE name = '$name' AND appointment_day = '$calday' AND appointment_month = '$calmonth' AND appointment_year = '$calyear' AND location = '$location' ORDER BY name")or die(mysql_error());

if(mysql_affected_rows() == 0){
echo 'There is no apointments listed for '.  $_SESSION['user']['location'] .  ' ' . $_GET['calday'] . ' ' . $_GET['calmonth'] . ' ' . $_GET['calyear'] . '<br /><br /><a href="add_appointment_set.php?d='.$calday.'&m='.$calmonth.'&y='.$calyear.'">Click here to add a new appointment</a>';
exit();
};
echo '<center><h3>' . $calday . '/' . $calmonth . '/' . $calyear . '</h3></center>';
while ($calendarChecked = mysql_fetch_array($checkCalendarDay))    
{
extract($calendarChecked);
echo '
<table width="99%" border="0" cellpadding="3" cellspacing="3" onClick="document.location.href=\'add_appointment.php?rep='.$name.'\';" onMouseOver="this.bgColor=\'#F3F8FC\';" onMouseOut="this.bgColor=\'#FFFFFF\';" class="grey-body">
  <tr>
    <td>'.$appointment_time.' - '.$city_town.' - '.$location.' - '.$postcode_1.' - '.$company_name.'</td>
  </tr>
</table>';
}

Could one of you nice people please help me achieve this.

I have tried with foreach loop but could not get it right.

Thanks in advance.
ianhull
Forum Contributor
Posts: 310
Joined: Tue Jun 14, 2005 10:04 am
Location: Hull England UK

Post by ianhull »

Here is what I have tried

Code: Select all



<?php session_start();

$calday = $_GET['calday'];
$calmonth = $_GET['calmonth'];
$calyear = $_GET['calyear'];
$location = $_SESSION['user']['location'];

include_once("../_includes/connection.php");

$getUsers = mysql_query("SELECT 
id, 
firstname, 
lastname, 
location, 
level FROM users where level = 'User' AND location = '$location'")or die(mysql_error());  

$name = array();
while ($usersRecieved = mysql_fetch_array($getUsers))    
{ 
extract($usersRecieved); 
$name[] = $firstname . ' ' . $lastname;
}
foreach( $name as $value){
	echo "$value <br />";
		
$checkCalendarDay = mysql_query("SELECT 
id, 
name, 
company_name, 
appointment_time, 
appointment_day, 
appointment_month, 
appointment_year, 
city_town, 
location, 
postcode_1, 
postcode_2 FROM currentappointments WHERE name = '$value' AND appointment_day = '$calday' AND appointment_month = '$calmonth' AND appointment_year = '$calyear' AND location = '$location' ORDER BY name")or die(mysql_error()); 

if(mysql_affected_rows() == 0){ 
echo 'There is no apointments listed for '.  $_SESSION['user']['location'] .  ' ' . $_GET['calday'] . ' ' . $_GET['calmonth'] . ' ' . $_GET['calyear'] . '<br /><br /><a href="add_appointment_set.php?d='.$calday.'&m='.$calmonth.'&y='.$calyear.'">Click here to add a new appointment</a>'; 
exit(); 
}; 
}
echo '<center><h3>' . $calday . '/' . $calmonth . '/' . $calyear . '</h3></center>'; 
while ($calendarChecked = mysql_fetch_array($checkCalendarDay))    
{ 
extract($calendarChecked); 
echo ' 
<table width="99%" border="0" cellpadding="3" cellspacing="3" onClick="document.location.href=\'add_appointment.php?rep='.$name.'\';" onMouseOver="this.bgColor=\'#F3F8FC\';" onMouseOut="this.bgColor=\'#FFFFFF\';" class="grey-body"> 
  <tr> 
    <td>'.$appointment_time.' - '.$city_town.' - '.$location.' - '.$postcode_1.' - '.$company_name.'</td> 
  </tr> 
</table>'; 
} 
?>


any ideas?
rpadilla
Forum Newbie
Posts: 1
Joined: Thu Apr 12, 2007 12:04 am

Post by rpadilla »

if(mysql_affected_rows() == 0){
echo 'There is no apointments listed for '. $_SESSION['user']['location'] . ' ' . $_GET['calday'] . ' ' . $_GET['calmonth'] . ' ' . $_GET['calyear'] . '<br /><br /><a href="add_appointment_set.php?d='.$calday.'&m='.$calmonth.'&y='.$calyear.'">Click here to add a new appointment</a>';
exit();
should be

mysql_num_rows($checkCalendarDay) since you are selecting and not doing changes.
ianhull
Forum Contributor
Posts: 310
Joined: Tue Jun 14, 2005 10:04 am
Location: Hull England UK

Post by ianhull »

Thanks for that,

It does not solve my problem though,

has anyone else got any ideas.

Struggling with this one.

Thanks
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

mysql_affected_rows returns the number of rows that were modified for an UPDATE, INSERT, DELETE and a few other non-SELECT QUERIES.

What you are trying to do is grab a list of data, then while looping that data, loop a subset of data that is related to each iteration of the original loop, correct. Kinda like:

Category: Cars

Code: Select all

Maker:  Acura
  Class:  RL
  Class:  TL
Maker:  Audi
  Class:  A4
  Class:  A6
  Class:  TT
Maker:  BMW
  Class:  325
  Class:  525
Is that right?
Post Reply