Working with database data

[inter]

Hello, a beginners question about working with data from a mysql database:

 

Lets say I performed a query that gave me 20 records in a database, each with many fields (eg: ID, name, date, catagory, etc).

 

I dont want to print all that information right away in one big loop, though. I want to print little bits of it in various places on the page.

 

eg: In one box on the page, I want to print the name and ID of the records who are in catagory 'blue'. In another box, I may want to only print dates in catagory 'green'.

 

I'm guessing I save the initial mysql query as a variable/array/whatever it's called, and then duplicate and filter it using if() statements each of the places on the page I want to print things?

 

$query = "SELECT * FROM $database WHERE `catagory` = 'blue' ORDER BY `$database`.`Date` ASC";
$result = mysql_query($query);

How would I then use the '$result' to display only the desired records in multiple areas on the page?

 

Thanks!

[interpim]

build an additional array for the categories you want to give, then when you run through the results of your query, then you can put the information into your arrays around the page such as blue/green/red etc...

 

Or, you could have several queries... one for each box as you put it.  Basically just select what you want to use from your database... if you want to display stuff from category green only search for those in your query... but for each box have a seperate query.

[inter]

Hi Interpim, thanks for the response.

 

I thought about using multiple database queries, but each page has over 40 boxes, and there are around 150 pages that will be using the same method, with slight changes in each. Would all those queries slow the database (and the server that is handling the php?)

 

build an additional array for the categories you want to give, then when you run through the results of your query, then you can put the information into your arrays around the page such as blue/green/red etc...

 

That's the part I'm confused about. Previously when I worked with a bunch of records from a database, I just performed a query (like in the code I posted) and did a big while() loop with with $results in echo's to make a table or list of records. I just copied what a friend had done once, it looked like this:

 

<table>
<?php
$data = mysql_query("SELECT * FROM database WHERE $field LIKE'%$find%'");
while($result = mysql_fetch_array( $data ))
{ echo "<tr><td>$result['name'], $result['email']</td></tr>" }
?>
</table>

 

Which would make a nice list of all the records that were queried.

 

How do I make the while() loop only show the results that I want? Eg in my example code above, only to loop the records where the 'name' field is "Bob".

 

I tried doing this, but it looped all the records in the query, not just the ones that matched my if() statement:

 

...single query done at the top of the page...
<?php
while ($result = mysql_fetch_array( $data ))
{
  if ($result['Colour'] = 'blue')
  {
    echo $result['Name'] . '<br />' ;
    echo "etc etc"
  }
}

 

And when I tried to do it again down the page, I got an error.

 

[teng84]

maybe you dont need the if inside the loop you only need to limit your query like this

$data = mysql_query("SELECT * FROM database WHERE name ='bob'  and color='blue'");

that will only return all the record that name bob and colored blue

 

so your loop look like this

<?php
$data = mysql_query("SELECT * FROM database WHERE name ='bob'  and color='blue'");
while($result = mysql_fetch_array( $data ))
{ echo "<tr><td>$result['name'], $result['email']</td></tr>" }
?>

[inter]

So you reckon it's fine to do upwards of 40 or 50 of those queries per page?

 

I really don't know what's standard when it comes to this... I just assumed it would cause very slow load times or bugged queries.

[teng84]

So you reckon it's fine to do upwards of 40 or 50 of those queries per page?

sorry i don't get this. can you explain this well

[inter]

So you reckon it's fine to do upwards of 40 or 50 of those queries per page?

sorry i don't get this. can you explain this well

 

The reason why I wanted to filter my single query (instead of using multiple queries) was because I would like to use the same data in many places on the page, but only show certain bits in certain areas. To be more specific:

 

I have a database with records in it. I can query that database and get a bunch of records out of it. With these records, I want to display the records with the field 'colour' as "blue" in one part of the page... then display all records with the field 'name' as "bob" to appear in another part of the page, etc.

 

But if I can simply make 40 mysql queries in the one page without any consequences, I'll just do that instead

[teng84]

i haven't see php mysql code having morethan 40-50 sub queries men this is wrong practice

you might need to join tables

 

can we see more of your code?

[KrisNz]

The reason this didn't work...

if ($result['Colour'] = 'blue')

Is because you've assigned a value rather than compared two values. use ==

 

Also heres a sample that might be helpful

 

    
<?php
//sql select * from fruits_n_veges
$dbData[0] = array("color"=>"green","name"=>"apple","type"=>"fruit");
$dbData[1] = array("color"=>"yellow","name"=>"lemon","type"=>"fruit");
$dbData[2] = array("color"=>"green","name"=>"broccoli","type"=>"vegetable");
$dbData[3] = array("color"=>"yellow","name"=>"sweetcorn","type"=>"vegetable");

$allMyFruitsNVeges = array();
//sort results into one multi-dimensional array organized by color
foreach ($dbData as $item) {
    $allMyFruitsNVeges[array_shift($item)][] = $item;
}

?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN">
<html lang="en">
<head>
    <title><!-- Insert your title here --></title>
</head>
<body>
<pre>
    
<?php print_r($allMyFruitsNVeges);?>

</pre>

<div id="Box1" style="border:1px solid green;margin-bottom:1em;">
    <h3>Greens</h3>
    <?php foreach ($allMyFruitsNVeges['green'] as $aDetails) :?>
        <p><?php echo "name is {$aDetails['name']}" ?></p>
        <p><?php echo "type is {$aDetails['type']}" ?></p>
    <?php endforeach;?>
</div>

<div id="Box2" style="border:1px solid yellow;">
    <h3>Yellows</h3>
    <?php foreach ($allMyFruitsNVeges['yellow'] as $aDetails) :?>
        <p><?php echo "name is {$aDetails['name']}" ?></p>
        <p><?php echo "type is {$aDetails['type']}" ?></p>
    <?php endforeach;?>
</div>
</body>
</html>

 

[inter]

The reason this didn't work...

if ($result['Colour'] = 'blue')

Is because you've assigned a value rather than compared two values. use ==

Ah! That explains a lot, thanks KrisNz. I thought stuff inside the if() is just like a maths equation... didn't realise you can assign stuff in there.

 

I'm trying to digest the code you posted. I've only recently learnt what an array is, and how to manipulate it (well, I'm still not 100% sure, but online tutorials have sorta helped). It seems that the code you posted does exactly what I need. Uses one mysql query and then filters out what I need for every section of the page. I'll spend a bit of time learning how it works so I can adapt it to my site.

 

 

i haven't see php mysql code having morethan 40-50 sub queries men this is wrong practice

you might need to join tables

 

can we see more of your code?

Actually, all the data is in one table. The reason why I thought of using many queries is just so I can select specific records using a SQL query (because I dont know how to select specific records in an array gotten from a mysql_query using php)

[inter]

Well I'm thinking I'll end up doing this: (because I cant understand how the array thing KrisNz posted)

 

In the header:

<?php

$ndoname = $_GET['n'];
$ndoname = strip_tags($ndoname);

// Connecting, selecting database
$link = mysql_connect('hidden', 'hidden', 'hidden')
    or die('Could not connect: ' . mysql_error());
mysql_select_db('hidden') or die('Could not select database');

?>

a bunch of formatting, html tags etc etc... first section of php in body:

<td valign="top"><div align="left"><a href="../day.php?n=<?php echo($ndoname); ?>&date=2008-02-01">1</a><br />
<?php
$query = "SELECT * FROM $ndoname WHERE `Date` = '2008-02-01' ORDER BY `$ndoname`.`Date` , `StartTime` ASC";
$result = mysql_query($query);
while ($row = mysql_fetch_assoc($result))
  {
    echo "<a href='../event.php?n=$ndoname&e=" . $row['Eventid'] . "'>" . $row['ProgramType'] . "</a><br />";
    echo 'Start Time: ' . $row['StartTime'] . '<br />';
    echo 'Finish Time: ' . $row['FinishTime'] . '<br />';
  }
?>
</div></td>

and another place it gets repeated:

<td valign="top"><div align="left"><a href="../day.php?n=<?php echo($ndoname); ?>&date=2008-02-02">1</a><br />
<?php
$query = "SELECT * FROM $ndoname WHERE `Date` = '2008-02-02' ORDER BY `$ndoname`.`Date` , `StartTime` ASC";
$result = mysql_query($query);
while ($row = mysql_fetch_assoc($result))
  {
    echo "<a href='../event.php?n=$ndoname&e=" . $row['Eventid'] . "'>" . $row['ProgramType'] . "</a><br />";
    echo 'Start Time: ' . $row['StartTime'] . '<br />';
    echo 'Finish Time: ' . $row['FinishTime'] . '<br />';
  }
?>
</div></td>

 

So basically, they're the same, but the first box does a mysql query for the records with the date '2008-02-01' and then echo's them, and the other box does the exact same but with the date '2008-02-02'.

 

Then I'll just need to repeat that for 365 days of the year.. for however many years I need. Copy/Paste yay.