PDA

View Full Version : Select all from database where multiple values match??? How to


JamesTaylor
12-03-2008, 05:03 PM
I've been going through DavidJ's PHP beginner tutorials, i've done them twice now and i must say they are very helpful since i am i noob to PHP.

The 1st time i simply followed his instructions, the 2nd i have followed them trough creating my own application... a latest news feature that i want to incorporate into a site i'm building.

I have everything working fine in that a user can now add, delete and update news items in the database. For the next part i want three of the news items to be displayed on the front page of the site and for the site adminstrator to be able to select which three item are displayed on the front page.

In order to achieve this i figured that if i add a "FrontPagePostion" field to the database the administrator can assign a value of 1, 2, 3 to the database records which will indicate the 3 items and their position on the front page.

What i'm struggling with is how to implement it!

Here is what i have so far which is working for a single value.


<?php
require_once("Connections/connection.php"); //database connection

//////////////////////////////////
$query = sprintf("SELECT * FROM news where FrontPagePosition = 1");
$result = @mysql_query($query);
$row = mysql_fetch_array($result);
//////////////////////////////////

do {
echo $row['Headline']."<br>";
echo $row['Description']."<br>";
}while ($row = mysql_fetch_array($result));

?>
how do i write the query so that it selects all from news where FrontPagePosition = 1 or 2 or 3???

and equally i presume if it can all be selected in one query it'll be brought from the database in an array / multi dimensional array??? how do i then target the each specific record so i can echo it onto the page as required??

I guess i could create 3 unique database queries - One to get FrontPagePosition 1, one to get FrontPagePosition 2, one to get FrontPagePosition 3 but i presume this wouldn't be very efficient coding or use of CPU resources etc.?

Thanks, looking forward to the repsonses!!

James

edbr
12-04-2008, 01:04 AM
maybe an if statement to echo them
if($row['FrontPagePosition']==1)
echo $row['Headline']."<br>";
etc

JamesTaylor
12-04-2008, 02:35 PM
Got thisworking using the following code


<?php
require_once("Connections/connection.php"); //database connection

//// This selects all from news d'base where FrontpagePosition is 1,2 or 3 and orders by FrontPagePosition/////////////////////////
$query = sprintf("SELECT * FROM news where FrontPagePosition IN (1,2,3) ORDER BY FrontPagePosition");
$result = @mysql_query($query);

//// This places results of query into an array - results can be accessed by $table[0]['Headline'] / $table[0]['Description'] etc.
$table = array();
while ($row = mysql_fetch_assoc($result)) {
$table[] = $row;
}
?>


i have then used


<?php echo $table[0]['Headline'] ?><br /><br />
<?php echo $table[0]['Description'] ?><br /><br />


to display the appropriate "field" on the page as required.

2bz2p
12-04-2008, 02:54 PM
You could (with a little testing) use a for loop to write the code once and have it dynamically change the 1, 2, and 3 Variable and it would out put all three.


$c = "3";
for ($i = 0; $c <= $i; $i++){ code to be executed;}

$i << its just the number to start with I suggest 0 or 1 depends on the array.

$c <= $i << this will check to see if $c is less than or equal to $i.

$i++ << this will increment $i by 1 every time the loop is run.

I would give you a better description though I am not at my programming computer right now

2b