Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    New Coder
    Join Date
    Sep 2011
    Posts
    78
    Thanks
    15
    Thanked 0 Times in 0 Posts

    IF Statement (select statement)

    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:

    Code:
    <?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>

  • #2
    Senior Coder
    Join Date
    Jan 2011
    Location
    Missouri
    Posts
    4,105
    Thanks
    23
    Thanked 594 Times in 593 Posts
    Do your if statement while your printing your menu.

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

    Change that line for a if/else statement.

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,211
    Thanks
    75
    Thanked 4,343 Times in 4,309 Posts
    I agree with sunfighter: This might be best done in PHP code.

    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:
    Code:
    	$sql = "
    	SELECT id, name, preptime, totaltime, IF(rating=0,'no rating',CONCAT('',rating)) AS rating
    	FROM recipes;
    	";
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    VickP07 (10-02-2011)

  • #4
    New Coder
    Join Date
    Sep 2011
    Posts
    78
    Thanks
    15
    Thanked 0 Times in 0 Posts
    Got it working Thanks!

  • #5
    Senior Coder
    Join Date
    Jan 2011
    Location
    Missouri
    Posts
    4,105
    Thanks
    23
    Thanked 594 Times in 593 Posts
    Thanks for the thank you.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •