Sorting Data from DB By Category HELP

[PHPAnx]

I may have put this in the wrong section the first time. My apologies for double posting. This is a very simple thing I know, I'm just stupid and can't figure it out. Please help me.

I'm trying to pull info from a single database. The info consists of 3 things:

-Show Category (guestShow)
-Date (guestAir)
-Artist to appear (guestArtist)

I want the results to look like this:

Comedy

12/16 Tom Smith
12/17 John Jones

Action

12/12 Todd Taylor
12/17 Derik Donk

Drama

12/10 Sam Simpleton
12/11 Eric Irons

Anyone have a clue? Here is some of my code I've been trying to get to work, but it just stinks:

$location = "localhost";
$username = "";
$password = "";
$database = "";

$conn = mysql_connect("$location","$username","$password");
if (!$conn) die ("Could not connect MySQL");
mysql_select_db($database,$conn) or die ("Could not open database");
return($conn);

$taco = "guestShow";
$limit = getDate();

foreach($taco as $key => $value) {

$sql = "SELECT * FROM guest WHERE guestShow";
$sql = $sql." AND guestShow = \"$key\"";
$sql = $sql." LIMIT \"$limit\"";

$result=mysql_query($sql) or die("eat me");

if (mysql_num_rows($result) !=0) {

print "Category: $value";

while ($line = mysql_fetch_array($result,MYSQL_ASSOC)) {

$air = $line["guestAir"];
$artist = $line["guestArtist"];

print "$air<br>$artist<br><br>";

}

print "<br>";

}

}

[Krait]

Uhm. This was written in a scratch buffer, so it's probably not valid code. Fixing any errors is left as an exerciese to the reader.

<?php
/* assuming $conn has been initialized */
function getCategoryData($cat='')
{
	global $conn;
	$q = sprintf("SELECT * FROM your_table WHERE guestShow = '%s'", addslashes($cat));
	$r = mysql_query($q);
	if (mysql_num_rows($r) == 0)
		return array();
	$data = array();
	while(($row=mysql_fetch_assoc($r) !== FALSE)
		$cats[] = $row;
	return $data;
}

function getCategories()
{
	global $conn;
	$q = "SELECT DISTINCT guestShow FROM your_table";
	$r = mysql_query($q);
	if (mysql_num_rows($r) == 0)
		return array();
	$cats = array();
	while(($cat=mysql_fetch_assoc($r) !== FALSE)
		$cats[] = $r['guestShow'];

return $cats;
}

$cats = getCategories();
foreach($cats as $cat)
{
	$shows = getCategoryData($cat);
	if (!empty($shows))
	{
		print '<ul>';
		printf('<b>%s</b>', $cat);
		foreach($shows as $show)
			printf('<li>%s %s</li>', $show['guestAir'], htmlentities($show['guestArtist']));
	}
}

?>

[PHPAnx]

I don't understand what you mean by it was written in a scratch buffer? I also don't understand where this fits in or anything. I tried to fit it in and added the correct table name (guest), but I get errors. Very confusing. Can you please explain a bit more.

[PHPAnx]

Does anyone have a less cryptic explanation on how to make this work?? I'm doing this for a non profit youth center for events.

We just need to display daily events by category.

[Sxooter]

OK, here's the pseudo code version:

$query = "select * from table order by category, air_date where ...";
execute that query, then do something like this:

$test="";  # blank test variable.
while ($row = fetch_array()){
  $cat = $row['category'];
  if ($cat!=$test) {
    print_header($cat);
    $test = $cat;
  }
  print_info($row);
}

function print_header($category){
   // code to print header goes here.
}

function print_info($row){
  //  code to print rows goes here.
}

[PHPAnx]

Can anyone point us towards an example or something similar of how to make this work. This is exactly what we need:

To display the events of the day by category.

For example, say today's date is 12/02. So today at the youth center:

Movies:

12/02 The Two Towers

Concerts:

12/02 Larry's Green Elephant

Discussions:

12/02 Safe Dating

Please help us out here.

Thanks

[PHPAnx]

Originally posted by Sxooter
OK, here's the pseudo code version:

$query = "select * from table order by category, air_date where ...";
execute that query, then do something like this:

$test="";  # blank test variable.
while ($row = fetch_array()){
  $cat = $row['category'];
  if ($cat!=$test) {
    print_header($cat);
    $test = $cat;
  }
  print_info($row);
}

function print_header($category){
   // code to print header goes here.
}

function print_info($row){
  //  code to print rows goes here.
}

[/B]

Where's the rest of the code? What is the correct select and what is "code to print header goes here?"

None of us can figure out these cryptic responses. We're not trying to be sarcastic, but the answers given seem more confusing than the original problem.

[Sxooter]

Sorry, but this forum is for helping you learn to program in PHP with a database. If you don't know how to program, it's unlikely you'll find someone here to do all the work for you.

Just put something in the print_header function that prints out the header the way you want (like print "category: $category"😉 etc...

[nova]

$location = "localhost"; 
$username = ""; 
$password = ""; 
$database = ""; 

$c = mysql_connect("$location","$username","$password"); 
mysql_select_db($database,$c);

$result = mysql_query("SELECT * FROM guest", $c);

// get all category names and place them in an array;

$i = 0;
while ($r = mysql_fetch_array($result))
{
  if (!in_array($r['guestShow'], $categories))
  {
    // if the category is not already in the array add it;

$categories[$i] = $r['guestShow'];
$i++;
  }
}

// run through the categories and print out the artists;

foreach ($categories as $cat)
{
  // print category name;

  print "<b>$cat</b><br><br>";

  $result = mysql_query("SELECT * FROM guest WHERE `guestShow`='$cat'", $c);

  // run through category guests and print them;

  while ($r = mysql_fetch_array($result))
  {
    print $r['guestAir'] . " " . $r['guestArtist'] . "<br>";
  }
}

That should work.

[PHPAnx]

Originally posted by nova

$location = "localhost"; 
$username = ""; 
$password = ""; 
$database = ""; 

$c = mysql_connect("$location","$username","$password"); 
mysql_select_db($database,$c);

$result = mysql_query("SELECT * FROM guest", $c);

// get all category names and place them in an array;

$i = 0;
while ($r = mysql_fetch_array($result))
{
  if (!in_array($r['guestShow'], $categories))
  {
    // if the category is not already in the array add it;

$categories[$i] = $r['guestShow'];
$i++;
  }
}

// run through the categories and print out the artists;

foreach ($categories as $cat)
{
  // print category name;

  print "<b>$cat</b><br><br>";

  $result = mysql_query("SELECT * FROM guest WHERE `guestShow`='$cat'", $c);

  // run through category guests and print them;

  while ($r = mysql_fetch_array($result))
  {
    print $r['guestAir'] . " " . $r['guestArtist'] . "<br>";
  }
}

That should work. [/B]

That works perfectly! But it is giving us this error:

Warning: Wrong datatype for second argument in call to in_array in /wh36688/public_html/6.php on line 29

Line 29 is this:

if (!in_array($r['guestShow'], $categories))

[PHPAnx]

How do you limit results to current day or date only?

[nova]

Originally posted by PHPAnx
That works perfectly! But it is giving us this error:

Warning: Wrong datatype for second argument in call to in_array in /wh36688/public_html/6.php on line 29

Line 29 is this:

if (!in_array($r['guestShow'], $categories))

This is because when the script runs through it the first time, its not an array yet. This should fix it however.

if (!@in_array($r['guestShow'], $categories))

[nova]

Originally posted by PHPAnx
How do you limit results to current day or date only?

You will need to look into the mktime() and date() functions. You can write the integer date timestamp into the mysql database instead of the date and then format it using date().

You can then use your field to fetch your result from x seconds ago, so figure out how many seconds are in a day.

// seconds in day;

$day = 60 * 60 * 24;

$start_date = mktime() - $day;

// select all data thats younger than one day back

$result = mysql_query("SELECT * FROM guest WHERE `guestShow`>'$start_date'", $c);

This code will select all entries that are 24 hours old or less from guest. You can also use similar code to go 24 hours into the future.

[PHPAnx]

Thank you Nova for all the help. The comment lines you included are really helpful in understanding the logic of the code. Thanks!