getting multiple values from 1 query

(2 Pages)
1
2
→

16 Replies - 1609 Views - Last Post: 17 September 2008 - 06:38 AM Rate Topic:

#1 dannzyx

New D.I.C Head

Reputation: 0

Posts: 27
Joined: 16-September 08

getting multiple values from 1 query

Post icon Posted 16 September 2008 - 04:36 AM

Hey people,

I solved my last problem with comparing data, it has been worked out with a lot of searching on the web.
Now I got another one.
This is the code:

$query="SELECT * FROM inschrijf";
$result=mysql_query($query);
$num=mysql_numrows($result);

$partijid=mysql_result($result,$i,"partij_id");
$user=mysql_result($result,$i,"usernaampje");

So I need to compare partij_id with some other id's in on a list.
The problem is there are multiple partij_id's in the table that need to be compared and this code only gives me one value to compare with (in my case 29 which is correct).

So is there a possibility solve this?
Thanks in advance!

Is This A Good Question/Topic? 0

Replies To: getting multiple values from 1 query

#2 pemcconnell

D.I.C Regular

Reputation: 54

Posts: 472
Joined: 05-August 08

Re: getting multiple values from 1 query

Posted 16 September 2008 - 05:28 AM

Hope I have understood your query correctly.

If you can collect all the partij_id's into an array before the initial query is executed, you can use the MySQL 'IN' clause:

e.g.

$sql = "SELECT * FROM table WHERE id IN (".$partij_id.")"; // where partij_id is an array of the values you wish to search your db for
// PS, you should never use 'SELECT *' - its far quicker to specify your columns

This post has been edited by pemcconnell: 16 September 2008 - 05:37 AM

Was This Post Helpful? 0

#3 dannzyx

New D.I.C Head

Reputation: 0

Posts: 27
Joined: 16-September 08

Re: getting multiple values from 1 query

Posted 16 September 2008 - 06:17 AM

pemcconnell, on 16 Sep, 2008 - 05:28 AM, said:

Hope I have understood your query correctly.

If you can collect all the partij_id's into an array before the initial query is executed, you can use the MySQL 'IN' clause:

e.g.

$sql = "SELECT * FROM table WHERE id IN (".$partij_id.")"; // where partij_id is an array of the values you wish to search your db for
// PS, you should never use 'SELECT *' - its far quicker to specify your columns

Ah ok, thanks for the advice. But in the meanwhile I went searching for another solution and I found one in the while loop. So I now have this:

<?php
include("../config.php");

$usertje=$_SESSION['naam'];		
?>


<html>

<head>
	<title>Partijen</title>
	
	<link rel="stylesheet" type="text/css" href="../style.css">
</head>

<body>

<form action="partijen_inschrijf_verwerk.php" method="GET">
	<p><b>Partijen </b></p>
	<table border="0" cellpadding="0" cellspacing="0" width="70%">
	 
	   <tr>
	   		<td>Beschikbaar</td>
	   		<td>Datum</td>
			<td>Arrangement</td>
			<td>Status</td>
			
		</tr>
		<tr>
			<td>&nbsp;</td>
			<td>&nbsp;</td>
			
		</tr>
		<?
		
		   
		$query = mysql_query("SELECT * FROM  `Partijen` ORDER BY  `Datum` ASC ");
			while($rij = mysql_fetch_assoc($query))		  
			{
				echo "
				<tr>
					<td>";
					
		$query2 = mysql_query("SELECT * FROM  `inschrijf`");
			while($rij2 = mysql_fetch_assoc($query2))
			
			{ 
			 $testing=($rij2['partij_id']);
			 $using=($rij2['usernaampje']);
				
		 	
					if($rij['id'] == $testing && $using == $usertje) { 
					echo "<strong>reeds ingeschreven </strong>"; 
					}
				
					else { 
					echo "<a href=\"partijen_inschrijf_verwerk.php?partij=". $rij["id"] ."\">schrijf in </a>"; 
					}
					
	   }  
					echo "</td> 
					<td>".ucfirst($rij['Datum'])."</td>
					<td>".ucfirst($rij['Arrangement'])."</td>
					<td>".ucfirst($rij['Status'])."</td>
					<td>$usertje</td>
										   
				   	"; 
				echo "</td>
				</tr>
			   
				";
			}
		
		
		?>
		
		</form>
	</table>
</body>

</html>

But when I ever I press the button that registers me as a participant for an certain event the amount of times the text that says "schrijf in" and "reeds geregistreerd" doubles, so after a while these two words take up my whole screen.

Was This Post Helpful? 0

#4 dannzyx

New D.I.C Head

Reputation: 0

Posts: 27
Joined: 16-September 08

Re: getting multiple values from 1 query

Posted 16 September 2008 - 06:39 AM

But coming back to your solution which seems to be much easier.
I got this now.

		<?
		
		   
		$query = mysql_query("SELECT * FROM  `Partijen` ORDER BY  `Datum` ASC ");
			while($rij = mysql_fetch_assoc($query))		  
			{
 				$sql = "SELECT partij_id FROM inschrijf WHERE id IN (". $rij['id'] .")"; 
				$sql2 = (mysql_fetch_assoc($sql));
 
				echo "
				<tr>
					<td>";

			   	
		$query2 = mysql_query("SELECT * FROM  `inschrijf`");
			while($rij2 = mysql_fetch_assoc($query2))
			
			{ 
			 $testing=($rij2['partij_id']);
			 $using=($rij2['usernaampje']);
				
		 	
					if($rij['id'] == $testing && $using == $usertje) { 
					echo "<strong>reeds ingeschreven </strong>"; 
					}
				
					else { 
					echo "<a href=\"partijen_inschrijf_verwerk.php?partij=". $rij["id"] ."\">schrijf in </a>"; 
					}
					
	   }  
					echo "</td> 
					<td>".ucfirst($rij['Datum'])."</td>
					<td>".ucfirst($rij['Arrangement'])."</td>
					<td>".ucfirst($rij['Status'])."</td>
					<td>$usertje</td>
					<td>$sql2</td>
										   
				   	"; 
				echo "</td>
				</tr>
			   
				";
			}
		
		
		?>

Cant get it to work

Could you get me trough it?
thanks in advance.

Was This Post Helpful? 0

#5 JackOfAllTrades

Saucy!

Reputation: 5723

Posts: 22,635
Joined: 23-August 08

Re: getting multiple values from 1 query

Posted 16 September 2008 - 08:06 AM

Need some more info to understand. Can there be more than one partij_id in the inshrijf table for each id in the Partijen table?

What is the relationship between the two tables? Can you provide the schema for these two tables?

Was This Post Helpful? 0

#6 dannzyx

New D.I.C Head

Reputation: 0

Posts: 27
Joined: 16-September 08

Re: getting multiple values from 1 query

Posted 16 September 2008 - 10:35 AM

It's made a small website for my internship employee to register and plan his employees for events.
I have two tables here, one for the employees containing username password firstname lastname etc.
And one for the events containing dates detailes etc.

Now I made a little system where the employees can register themselves for one or multiple events.
The script doing that takes the unique ID of the selected event and puts it in a 3rd table together with the current session username and all it's related data.

Table 1: loginsysteem
-id
-username
-password
-email
-firstname
-lastname

Table 2: partijen
-id
-date
-arrangement (dutch for the type of event)
-persons
-groups
-comments

Table 3: inschrijf
-id
-partij_id
-username
-firstname
-lastname

And yes there are going to be more partij_id's in the inschrijf table, because we have like 30 people working for us and each of them have to be able to register and sign up for events. And per event we need about 5 to 20 people to sign up.

So I now have this, it works fine except for the fact that whenever I click on 'schrijf in' (dutch for sign up) it says already registered but also expands the table with the sign up and already registered texts.

Code:

<?php
include("../config.php");



$usertje=$_SESSION['naam'];
  
		
?>


<html>

<head>
	<title>Partijen</title>
	
	<link rel="stylesheet" type="text/css" href="../style.css">
</head>

<body>

<form action="partijen_inschrijf_verwerk.php" method="GET">
	<p><b>Partijen </b></p>
	<table border="0" cellpadding="0" cellspacing="0" width="70%">
	 
	   <tr>
	   		<td>Beschikbaar</td>
	   		<td>Datum</td>
			<td>Arrangement</td>
			<td>Status</td>
			
		</tr>
		<tr>
			<td>&nbsp;</td>
			<td>&nbsp;</td>
			
		</tr>
		<?
		
		   
		$query = mysql_query("SELECT * FROM  `Partijen` ORDER BY  `Datum` ASC ");
			while($rij = mysql_fetch_assoc($query))		  
			{
		$query2 = mysql_query("SELECT * FROM  `inschrijf`");
			while($rij2 = mysql_fetch_assoc($query2))
			 { 
			 $testing=($rij2['partij_id']);
			 $using=($rij2['usernaampje']);  

				echo "
				<tr>
					<td>";
	 	

			
		   
				
		 	
					if($rij['id'] == $testing && $using == $usertje) { 
					echo "<strong>reeds ingeschreven </strong>"; 
					}
				
					else { 
					echo "<a href=\"partijen_inschrijf_verwerk.php?partij=". $rij["id"] ."\">schrijf in </a>"; 
					}
					
	   }  
					echo "</td> 
					<td>".ucfirst($rij['Datum'])."</td>
					<td>".ucfirst($rij['Arrangement'])."</td>
					<td>".ucfirst($rij['Status'])."</td>
					<td>$usertje</td>
										   
				   	"; 
				echo "</td>
				</tr>
			   
				";
			}
		
		
		?>
		
		</form>
	</table>
</body>

</html>

Was This Post Helpful? 0

#7 dannzyx

New D.I.C Head

Reputation: 0

Posts: 27
Joined: 16-September 08

Re: getting multiple values from 1 query

Posted 16 September 2008 - 11:51 PM

anyone?

Was This Post Helpful? 0

#8 pemcconnell

D.I.C Regular

Reputation: 54

Posts: 472
Joined: 05-August 08

Re: getting multiple values from 1 query

Posted 17 September 2008 - 01:44 AM

I took the liberty of tidying up your code there, and also putting a where clause into your second MySQL query, which 'I think' might be the area where you need to address.

In your code you did not have any WHERE clause in the second SQL statement which meant that as it was looping through the first MySQL results set, it would have been repeating the results of the second query.

There were also some HTML erros which would have caused that 'streching'.

Here's the new code:

<?php
require_once("../config.php");//Assume you have database connection and session_start(); in here
$usertje=$_SESSION['naam'];
?>
<html>

<head>
	<title>Partijen</title>
	<link rel="stylesheet" type="text/css" href="../style.css" />
</head>

<body>
<p><b>Partijen </b></p>
<form action="partijen_inschrijf_verwerk.php" method="GET">
	<table border="0" cellpadding="0" cellspacing="0" width="70%">
		<tr>
			<td>Beschikbaar</td>
			<td>Datum</td>
			<td>Arrangement</td>
			<td>Status</td>
		</tr>
		<tr>
			<td colspan="4">&nbsp;</td>
		</tr>
		<?php
		$query = mysql_query("SELECT id FROM  `Partijen` ORDER BY  `Datum` ASC ");
		while($rij = mysql_fetch_assoc($query)){
			$query2 = mysql_query("SELECT partij_id, usernaampje FROM  `inschrijf` WHERE partij_id = ".(int)$rij['id']);
			//START LOOPING THROUGH ITEMS
			while($rij2 = mysql_fetch_assoc($query2)){
				$testing=($rij2['partij_id']);
				$using=($rij2['usernaampje']);  
				//START TABLE ROW - THERE IS ONLY 1 TABLE CELL (<td>) IN THIS ROW - IS THIS RIGHT?
				echo "<tr>\n<td colspan=\"4\">\n";
		 		if(($rij['id'] == $testing) && ($using == $usertje)) {
					echo "<strong>reeds ingeschreven </strong>";
				} else {
					echo "<a href=\"partijen_inschrijf_verwerk.php?partij=". $rij["id"] ."\">schrijf in </a>";
				}
				echo "</td>\n</tr>\n";
				//END TABLE ROW
			}
			//END LOOP
			echo "<tr>\n<td>".ucfirst($rij['Datum'])."</td>\n";
			echo "<td>".ucfirst($rij['Arrangement'])."</td>\n";
			echo "<td>".ucfirst($rij['Status'])."</td>\n";
			echo "<td>".$usertje."</td>\n</tr>\n";
		}
		?>
	</table>
</form>
</body>
</html>

This won't be the solution as I had to guess what you were wanting to do, but it should point you in the right direction.

Hope it helps ya dude

This post has been edited by pemcconnell: 17 September 2008 - 01:47 AM

Was This Post Helpful? 0

#9 dannzyx

New D.I.C Head

Reputation: 0

Posts: 27
Joined: 16-September 08

Re: getting multiple values from 1 query

Posted 17 September 2008 - 03:52 AM

After a couple of adjustments it works perfectly thanks mate!
Only one thing, the 'else' rule with the "partijen_inschrijf_verwerk.php?" has dissapeared. Have tried everything and it doesn't seem to come back.
any idea's ?

Was This Post Helpful? 0

#10 pemcconnell

D.I.C Regular

Reputation: 54

Posts: 472
Joined: 05-August 08

Re: getting multiple values from 1 query

Posted 17 September 2008 - 04:06 AM

If neither 'reeds ingeschreven' nor 'schrijf in' Are displaying then there are no results found in the query 'SELECT partij_id, usernaampje FROM `inschrijf` WHERE partij_id = ".(int)$rij['id']);'.

On the other hand, if 'reeds ingeschreven' is always showing then ($rij['id'] == $testing) && ($using == $usertje) is never true. I have re-written the code so that you can debug it more accurately (you'll need to make whatever changes you made to the last code, to this snippet)

This should help you find your error - Good Luck

<?php
require_once("../config.php");//Assume you have database connection and session_start(); in here
$usertje=$_SESSION['naam'];
?>
<html>

<head>
	<title>Partijen</title>
	<link rel="stylesheet" type="text/css" href="../style.css" />
</head>

<body>
<p><b>Partijen </b></p>
<form action="partijen_inschrijf_verwerk.php" method="GET">
	<table border="0" cellpadding="0" cellspacing="0" width="70%">
		<tr>
			<td>Beschikbaar</td>
			<td>Datum</td>
			<td>Arrangement</td>
			<td>Status</td>
		</tr>
		<tr>
			<td colspan="4">&nbsp;</td>
		</tr>
		<?php
		$query = mysql_query("SELECT id FROM  `Partijen` ORDER BY  `Datum` ASC ");
		while($rij = mysql_fetch_assoc($query)){
			$query2 = mysql_query("SELECT partij_id, usernaampje FROM  `inschrijf` WHERE partij_id = ".(int)$rij['id']);
			//START LOOPING THROUGH ITEMS
			if(mysql_num_rows($query2)>0){
				while($rij2 = mysql_fetch_assoc($query2)){
					$testing=($rij2['partij_id']);
					$using=($rij2['usernaampje']);  
					//START TABLE ROW - THERE IS ONLY 1 TABLE CELL (<td>) IN THIS ROW - IS THIS RIGHT?
					echo "<tr>\n<td colspan=\"4\">\n";
					/*ORIGIONAL - UNCOMMENT THIS AND REMOVE THE NEW IF ELSE STATEMENT WHEN YOU ARE FINISHED DEBUGGING
					if(($rij['id'] == $testing) && ($using == $usertje)) {
						echo "<strong>reeds ingeschreven </strong>";
					} else {
						echo "<a href=\"partijen_inschrijf_verwerk.php?partij=". $rij["id"] ."\">schrijf in </a>";
					}*/
					//NEW
					if(($rij['id'] == $testing) && ($using == $usertje)) {
						echo "<strong>reeds ingeschreven. TESTING: id=".$rij['id'].", $testing=".$testing.", $using=".$using.", $usertje=".$usertje."</strong>";
					} else {
						echo "<a href=\"partijen_inschrijf_verwerk.php?partij=". $rij["id"] ."\">schrijf in </a>";
					}
					echo "</td>\n</tr>\n";
					//END TABLE ROW
				}
			}else{
				echo "<tr><td colspan=\"4\" style=\"font-weight:bold;\">No results found for 'SELECT partij_id, usernaampje FROM  `inschrijf` WHERE partij_id = ".(int)$rij['id'])."'</td></tr>";
			}
			//END LOOP
			echo "<tr>\n<td>".ucfirst($rij['Datum'])."</td>\n";
			echo "<td>".ucfirst($rij['Arrangement'])."</td>\n";
			echo "<td>".ucfirst($rij['Status'])."</td>\n";
			echo "<td>".$usertje."</td>\n</tr>\n";
		}
		?>
	</table>
</form>
</body>
</html>

This post has been edited by pemcconnell: 17 September 2008 - 04:10 AM

Was This Post Helpful? 0

#11 dannzyx

New D.I.C Head

Reputation: 0

Posts: 27
Joined: 16-September 08

Re: getting multiple values from 1 query

Posted 17 September 2008 - 04:27 AM

its neither of those 2

'Reeds ingeschreven' is displaying whenever it's suppose to, only when 'schrijf in' is suppose to be displaying it stays empty and the whole tablerow shifts to the left.
Going to try your debugging script now.

edit:

Ok so I tried the debugging script, and indeed there were
"No results found for 'SELECT partij_id, usernaampje FROM `inschrijf` WHERE partij_id = ".(int)$rij['id'])."'"

So I know there is a way to echo something when there are no results found.
Something with numrows correct?

This post has been edited by dannzyx: 17 September 2008 - 04:39 AM

Was This Post Helpful? 0

#12 pemcconnell

D.I.C Regular

Reputation: 54

Posts: 472
Joined: 05-August 08

Re: getting multiple values from 1 query

Posted 17 September 2008 - 04:56 AM

its already set up in that debugging script. Just change the line that says:

"No results found for 'SELECT partij_id, usernaampje FROM `inschrijf` WHERE partij_id = ".(int)$rij['id'])."'"

to whatever you want

Was This Post Helpful? 1

#13 dannzyx

New D.I.C Head

Reputation: 0

Posts: 27
Joined: 16-September 08

Re: getting multiple values from 1 query

Posted 17 September 2008 - 05:23 AM

mate, id like to thank you very much.
I've been at this script for 3 days now.
You fixed it within a couple of hours.
And you made me understand php better.

THANK YOU!!!!!!!!

Was This Post Helpful? 0

#14 pemcconnell

D.I.C Regular

Reputation: 54

Posts: 472
Joined: 05-August 08

Re: getting multiple values from 1 query

Posted 17 September 2008 - 05:35 AM

No probs dude, glad I could help

Any time you run into any problems just post em up and myself, or another D.I.C'er will be glad to help.

Happy coding

Was This Post Helpful? 0

#15 dannzyx

New D.I.C Head

Reputation: 0

Posts: 27
Joined: 16-September 08

Re: getting multiple values from 1 query

Posted 17 September 2008 - 06:07 AM

Back already

Still works fine.
But when I click for example all 'schrijf in' with my own user 'dannzyx' it perfectly says 'reeds ingeschreven' but when I then switch to the test user it perfectly displays that all options are open.

But when I click one of them it keeps creating more Tablerows with the 'schrijf in' option with the rest of the row blank while it also says 'reeds ingeschreven'.

Cant find the solution in this.
Maybe better if you see it.
i'll PM u the url and details.

Was This Post Helpful? 0

(2 Pages)
1
2
→