Separate 1 SELECT Statement into different results

How would I go about splitting 1 SELECT query into different outputs based on the value of category_id?

$sql = "SELECT venue_id, category_id
FROM tbl_venues
	$result = mysqli_query($sql) or die(mysqli_error());
	$row = mysqli_fetch_array($result);

i know I can display the full list of venues, but each venue is assigned to a category_id and I need to display a list of venues according to their category_id in separate places.

I’ve done this before using a number of different SELECT statements, I’m wondering if there is a easier way using 1 SELECT statement, I’ve post in the mysql forum regarding the stored procedure approach, just wondering if this can be done with php

Select all, then echo into separate containers
example:

<div id=“cat1”>
venues with cat1
</div>

<div id=“cat2”>
venues with cat2
</div>

<div id=“cat3”>
venues with cat3
</div>

I did come across:

/* Create a prepared statement */
   if($stmt = $mysqli -> prepare("SELECT priv FROM testUsers WHERE username=?
   AND password=?")) {

      /* Bind parameters
         s - string, b - boolean, i - int, etc */
      $stmt -> bind_param("ss", $user, $pass);

      /* Execute it */
      $stmt -> execute();

      /* Bind results */
      $stmt -> bind_result($result);

      /* Fetch the value */
      $stmt -> fetch();

      echo $user . "'s level of priviledges is " . $result;

      /* Close statement */
      $stmt -> close();
   }

   /* Close connection */
   $mysqli -> close();

Is this something I could use?

Thanks :cool:

You could order the results by the category id, so that when using a loop to display the contents you can look or a change in the category id and then start a new container for them.

Or, you could loop through the results and use the category id as an associative identifier in a new array, in which to store the results.

So which way is best?
I’ll also want to display a couple of other columns but will be basing everything around the category_id.

From above:

$sql = "SELECT venue_id, category_id
FROM tbl_venues
    $result = mysqli_query($sql) or die(mysqli_error());
    $row = mysqli_fetch_array($result);

Then some how display the below on different parts of the page:

a list of venues with the category_id = ‘disco’;
a list of venues with the category_id = ‘party’;

or maybe

/* create a prepared statement */
$stmt =  $mysqli->stmt_init();
if ($stmt->prepare("SELECT venue_id FROM tbl_venues WHERE category_id=?")) {

then output category_id = $???

Could you show me how the code would work, an example of how things would fit together thanks.

And what do you think about the mysqli stored procedures, would this be a better approach or could we tie this in together?

Also, I currently have mysql should I change everything to mysqli?

Just trying to get a better understanding thanks :cool:

I fit helps, what I was using for the separate pages, but now I want the same effect into a jquery tabbing system on the same page.

<? 

		$sql = "SELECT venue_id,
		FROM tbl_venues
		WHERE category_id='" .mysql_real_escape_string(str_replace("-"," ",(strtolower(ucwords($_GET['category_id'])))))."' AND active=1
                ORDER BY venue_id ASC";
		$result = mysql_query($sql) or die(mysql_error());
		$DATA=array();
                while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
		$DATA[]=$row;
		}
        $num_rows=mysql_num_rows($result);

?>
<? if($num_rows): ?>
<? foreach($DATA as $row): ?>
        <dl>
            <dt class="thumb-img"><img src="images/<?=$row['photo_sml']?>"></dt>
        <dt><a href="venue/<?=$row['venue_id']?>"><?=$row['slug']?></a></dt>
        
        <dd><p><?=$row['short']?> <a href="venue/<?=$row['url']?>">read more</a></p></dd></dl>

<? endforeach ?>
<? else: ?>
 <p>No data found</p>
<? endif ?>

Dont be confused by photo_sml, url these are other columns I’ve removed from the query for sitepoint thanks.

Some how I need a way of pasting the above snippet a number of times for the different category_id’s but using 1 SELECT query.

Thanks in advance.

You could order the results by the category id, so that when using a loop to display the contents you can look or a change in the category id and then start a new container for them.

Or, you could loop through the results and use the category id as an associative identifier in a new array, in which to store the results.

I also have a snippet below similar to the above, could anyone please give a small example of how I could output the different category_ids into different containers? :cool:

ok

$sql = "SELECT venue_id AS venue, category_id, address
                FROM   tbl_venues";

                $result = mysql_query($sql);

                if (!$result) {
                    echo "Could not successfully run query ($sql) from DB: " . mysql_error();
                    exit;
                }

                if (mysql_num_rows($result) == 0) {
                    echo "No rows found";
                    exit;
                }

                while ($row = mysql_fetch_assoc($result)) {
                    echo $row["venue"];
                    echo $row["category_id"];
                    echo $row["address"];
                }

                mysql_free_result($result);