...

View Full Version : IF Statement (select statement)



VickP07
10-01-2011, 09:20 AM
I didn't know what to put as the title sorry!

Okay guys here is was i am doing. I am displaying in a table some data from a database. The table shows
Recipe Name | Prep Time | Total Time | Rating
====================================
some data 20 50 4.8
some data 20 50 3.2
some data 20 50 0

Okay so here is was I want to do:
If the rating value = 0 then display inside the table "no rating"

I was thinking in my php code to do an if statement to test for if my 'rating' field in my database is = 0 then display the string "no rating"

Would i use something like mysql_query() function?

Here is my code for my home page displaying the table with the data:



<?php
$db = mysql_connect( "localhost","root", "temp1234");
mysql_select_db( "Peters_restaurantDB");

$sql = "
SELECT id, name, preptime, totaltime, rating
FROM recipes;
";

#test if select statement works
$result = mysql_query( $sql );
$result or die("My query ($sql) failed." );

if (mysql_num_rows ($result ) == 0 )
{
$err = true;
}
?>

<html>

<head>
<title>3342 Recipe DB</title>
</head>

<body>
<h2>Recipes in the Database...</h2><hr />

<p></p>
<h3>*Click on a recipe name to view additional info on it. </h3>
<h3>***You can also sort the recipes based on Recipe Name, Prep Time, Total time, or Rating. </h3>
<p></p>
<p></p>

<!--The code below creates the table to display all the data from the DB. The headers of all the column contain a link
to this same php file. The link allows the user to sort based on Recipe name, preptime, total time, and rating
I used a switch statement to test for what the user clicks on to sort, and then i pass the sql query.
-->

<table border="2" width ="500">
<tr>
<th><a href="PetersRecipeDB.php?sort=recipe">Recipe Name</a></th>
<th><a href="PetersRecipeDB.php?sort=prep">Prep Time</a></th>
<th><a href="PetersRecipeDB.php?sort=total">Total Time</a></th>
<th><a href="PetersRecipeDB.php?sort=rating">Rating</a></th>
</tr>

<?
$sortswitch = $_GET['sort'];

switch ($sortswitch) {
case "recipe":
$sql = "SELECT id, name, preptime, totaltime, rating FROM recipes ORDER BY name";
break;
case "prep":
$sql = "SELECT id, name, preptime, totaltime, rating FROM recipes ORDER BY preptime";
break;
case "total":
$sql = "SELECT id, name, preptime, totaltime, rating FROM recipes ORDER BY totaltime";
break;
case "rating":
$sql = "SELECT id, name, preptime, totaltime, rating FROM recipes ORDER BY rating";
break;
default:
$sql = "SELECT id, name, preptime, totaltime, rating FROM recipes";
break;
}

#test if select statement works
$result = mysql_query( $sql );
$result or die("My query ($sql) failed." );

#While statement used to pass data from DB into tables -->

while( $row = mysql_fetch_array( $result ) ) { ?>

<form name="input" action="ShoppingList.php" method="get">

<tr>

<td><a href="ShowRecipe.php?id=<?=$row['id'] ?>"><input type="checkbox" name="r_name[]" value="<?=$row['id']?>" /><?=$row['name'] ?></a></td>
<td><?=$row['preptime']?> mins</td>
<td><?=$row['totaltime']?> mins</td>
<td><?=$row['rating']?></td>
</tr>
<? } ?>

</table>


<input type="submit" value="Submit" />
</form>
<p>If you click the "Submit" button, the shopping list page will be displayed for the recipe(s) you check.</p>

<p><a href="addnewrec.php">Add Recipe</a></p>

<hr />
<p>The current date and time is: </h4></p> <code> <?php print strftime('%a') ?> <?php print strftime('%b') ?> <?php print strftime('%e') ?> <?php print strftime('%r') ?> <?php print strftime('%G')?> </code>

</body>
</html>

sunfighter
10-01-2011, 01:49 PM
Do your if statement while your printing your menu.

<td><?=$row['rating']?></td>

Change that line for a if/else statement.

Old Pedant
10-01-2011, 11:55 PM
I agree with sunfighter: This might be best done in PHP code.



$rating = $row["rating"];
if ($rating == 0 ) $rating = "no rating";
echo "<td>" . $rating ."</td>\n";

HOWEVER...

You *can* do this in the SQL query. Yes, you can use the function form of IF. But realize that [b]IF[b] expects both "branches" of the if (true and false) to return the same data type. Since "no rating" is a string (e.g., VARCHAR), you need to convert the number to a string as well. Easy to do, of course:


$sql = "
SELECT id, name, preptime, totaltime, IF(rating=0,'no rating',CONCAT('',rating)) AS rating
FROM recipes;
";

VickP07
10-02-2011, 06:52 AM
Got it working Thanks!

sunfighter
10-02-2011, 07:53 PM
Thanks for the thank you.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum