Query's and loops

[XistenceNL]

Hi @ all,

 

I'm building a simpel agenda function for my boss 

Now I've ran in some basic query problems.

 

First the code:

 

<?
include_once('../../config/config.php');

$query      =  "SELECT id, bedrijfsnaam, bezoeken FROM clients WHERE actief = 'ja' AND bezoeken > 0 ORDER BY id ASC";
$result     =  mysql_query($query)or die ("Query kon niet worden uitgevoerd");

echo '<table style="border-collapse:collapse;">';

while($row  =  mysql_fetch_assoc($result)){
extract($row);

$bedrijfsnaam = ucfirst($bedrijfsnaam);
$bedrijfsnaam = htmlentities($bedrijfsnaam);

if($rowcount % 2 == 0) {
echo '<tr onMouseover="this.style.backgroundColor=\'#649fbe\'"; onMouseout="this.style.backgroundColor=\'#fff\';" style="background-color:#fff; border-bottom: 1px solid black;">';
} else {
echo '<tr onMouseover="this.style.backgroundColor=\'#649fbe\'"; onMouseout="this.style.backgroundColor=\'#f2f2f1\';" style="background-color: #f2f2f1; border-bottom: 1px solid black;">';
}
        $rowcount ++;


// huidige jaar ophalen
$huidig_jaar = date("Y");

echo'
<td style="width: 313px; font-family: verdana; font-size: 10px; font-weight: bold; min-height: 20px;">'.$bedrijfsnaam.'</td>
<td style="width: 8px; font-family: verdana; font-size: 10px; font-weight: bold;">'.$bezoeken.'</td>
<td style="width: 100px; text-align: center; border-left: 1px solid black;">';

//januari ophalen
$query1      =  "SELECT datum FROM planning WHERE id = '.$id.' AND YEAR(datum) = '$huidig_jaar' AND MONTH(datum) = '01' ORDER BY id ASC";
$result1     =  mysql_query($query1)or die ("Query 1 kon niet worden uitgevoerd");
$num1 		 =  mysql_num_rows($result1);

echo $num1;

echo'
</td>
<td style="width: 100px; text-align: center; border-left: 1px solid black;">';

//februari ophalen
$query2      =  "SELECT datum FROM planning WHERE id = '.$id.' AND YEAR(datum) = '$huidig_jaar' AND MONTH(datum) = '02' ORDER BY id ASC";
$result2     =  mysql_query($query2)or die ("Query 1 kon niet worden uitgevoerd");
$num2 		 =  mysql_num_rows($result2);

echo $num2;

echo'
</td>
<td style="width: 100px; text-align: center; border-left: 1px solid black;">';

//februari ophalen
$query3      =  "SELECT datum FROM planning WHERE id = '.$id.' AND YEAR(datum) = '$huidig_jaar' AND MONTH(datum) = '03' ORDER BY id ASC";
$result3     =  mysql_query($query3)or die ("Query 1 kon niet worden uitgevoerd");
$num3 		 =  mysql_num_rows($result3);

echo $num3;

echo'
</td>
<td style="width: 100px; text-align: center; border-left: 1px solid black;"></td>
<td style="width: 100px; text-align: center; border-left: 1px solid black;"></td>
<td style="width: 100px; text-align: center; border-left: 1px solid black;"></td>
<td style="width: 100px; text-align: center; border-left: 1px solid black;"></td>
<td style="width: 100px; text-align: center; border-left: 1px solid black;"></td>
<td style="width: 100px; text-align: center; border-left: 1px solid black;"></td>
<td style="width: 100px; text-align: center; border-left: 1px solid black;"></td>
<td style="width: 100px; text-align: center; border-left: 1px solid black;"></td>
<td style="width: 100px; text-align: center; border-left: 1px solid black;"></td>
</tr>
';
}

echo '</table>';

?>

 

In the first query I'll get the id from all the clients in the database, now I want to use that id's in the upcoming query's (WHERE id = '.$id.'), but it allways uses the first id and not all the id's. $num always has the same result.

 

Can anyone put me in the right direction? Thank you

 

Sorry for my bad English, I'm dutch, can't help it  I blame my parents...

I hope it's enought information....

 

 

Greetings XistenceNL

[Simon Mayer]

while($row  =  mysql_fetch_assoc($result)){
extract($row);

 

I think extract() is the problem. I suspect that when extract() has run once, id has already been defined and so the value is not overwritten the second time extract() is called. You could use EXTR_OVERWRITE, as per the PHP manual: http://php.net/manual/en/function.extract.php, but I really wouldn't recommend it.

I think that extract() probably causes more problems than it solves.

 

You would be better avoiding extract() and using  $row->id, $row->bedrijfsnaam, etc instead

[XistenceNL]

Thanks, I'll fix it and see if it solved the problem and saves my day 

[XistenceNL]

I have removed extract out of my code, but it doesn't make a difference 

 

$query      =  "SELECT id, bedrijfsnaam, bezoeken FROM clients WHERE actief = 'ja' AND bezoeken > 0 ORDER BY id ASC";
$result     =  mysql_query($query)or die ("Query kon niet worden uitgevoerd");

echo '<table style="border-collapse:collapse;">';

while($row  =  mysql_fetch_assoc($result)){

$id			  = $row['id'];
$bedrijfsnaam                            = $row['bedrijfsnaam'];
$bezoeken	                   = $row['bezoeken'];
$bedrijfsnaam                             = ucfirst($bedrijfsnaam);
$bedrijfsnaam                              = htmlentities($bedrijfsnaam);

[mikosiko]

is this your actual query?:

$query1      =  "SELECT datum FROM planning WHERE id = '.$id.' AND YEAR(datum) = '$huidig_jaar' AND MONTH(datum) = '01' ORDER BY id ASC";

 

because if it is you have an error there that could be causing the behavior... check the difference and try this:

$query1      =  "SELECT datum FROM planning WHERE id = $id  AND YEAR(datum) = '$huidig_jaar' AND MONTH(datum) = '01' ORDER BY id ASC";

[XistenceNL]

Thanks Mikosiko for the help,

 

It's true, I made the fault in my query 

I didn't had to use the dots @ $id in the query:

 

Here's the correct query:

$query1      =  "SELECT datum FROM planning WHERE id = '$id' AND YEAR(datum) = '$huidig_jaar' AND MONTH(datum) = '01' ORDER BY id ASC";

 

Works perfect now.

 

Topci can be noted as solved!

[mikosiko]

Good.... please be sure to mark the post as "Solved" yourself... thanks