Create an Array from a MySQL database

dmirsch · October 26, 2011

I am getting the following error:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /data/23/2/100/53/2263053/user/2487001/htdocs/centerstage/centertix-ticketsellers/knowledgebase-forms.php on line 37
Warning: Invalid argument supplied for foreach() in /data/23/2/100/53/2263053/user/2487001/htdocs/centerstage/centertix-ticketsellers/knowledgebase-forms.php on line 41

Here is my code:

<?php
@$DB = mysqli_connect($server,$username,$password,$database);
if (mysqli_connect_errno()){
echo 'Cannot connect to database: ' . mysqli_connect_error();
} else {
$Query = 'SELECT KBcategory.Category, KBcategory.SUBcategory, knowledgeBase.faqID, knowledgeBase.SubCategoryID, knowledgeBase.CreationDate, knowledgeBase.ModificationDate, knowledgeBase.HomePage, knowledgeBase.Author, knowledgeBase.Views, knowledgeBase.Item, knowledgeBase.Description, knowledgeBase.Attachment, knowledgeBase.Image FROM knowledgeBase LEFT JOIN KBcategory ON knowledgeBase.SubCategoryID = KBcategory.kbcatID WHERE KBcategory.Category = "Forms" ORDER BY KBcategory.Category, KBcategory.SUBcategory';
$Result = mysqli_query($DB,$Query);
$subcats = mysql_fetch_array($Result);
$NumResults = mysqli_num_rows($Result);

foreach ($subcats  as $key => $Row['SUBcategory']) {
echo '<h1>'.$Row['SUBcategory']. '</h1>';
while ($Row = mysqli_fetch_assoc($Result)){
	echo '<h2>'. $Row['Item'] . '</h2>';
                 //CODE REMOVED AS IT'S NOT NECESSARY FOR MY QUESTION
	echo $Row['Description'];
echo '<hr /><hr />';
}
}
?>

What I would like is to have the first SUBcategory listing with all the pertinent Item/Description, then list the next SUBcategory with all the pertinent Item/Description, and so on... Where did I go wrong?

Pikachu2000 · October 26, 2011

Off the bat, I'd say the query is failing. What does mysqli_error() show? Once that's cleared up, you can work on formatting the output.

dmirsch · October 26, 2011

I know the query works, because I plug that right into my SQL in phpMyAdmin and it comes back with 7 results.

Pikachu2000 · October 26, 2011

Just spotted the problem. You're mixing mysql and mysqli extension functions. They aren't interchangeable.

dmirsch · October 26, 2011

OK, so I changed it all to mysqli functions, but it still does not help me to get what I originally wanted...

I would like is to have the first SUBcategory listing with all the pertinent Item/Description, then list the next SUBcategory with all the pertinent Item/Description, and so on...

It still lists it out as SUBcategory#1, Item/Description#A; then SUBcategory#1, Item/Description#B; then SUBcategory#1, Item/Description#C; then SUBcategory#2, Item/Description#A; then SUBcategory#2, Item/Description#B...

What I want is SUBcategory#1, Item/Description#A, Item/Description#B, Item/Description#C; then SUBcategory#2, Item/Description#A, Item/Description#B...

How do I get this to work?

PFMaBiSmAd · October 26, 2011

You would remember/detect when the subcategory changes and just output the heading when it does. See the $last_cat logic in the following similar thread - http://www.phpfreaks.com/forums/index.php?topic=342485.msg1615743#msg1615743

fenway · October 27, 2011

Or, pre-process the results, and make a hash.

polycap9 · October 31, 2011

hey pikachu What do you mean by "They aren't interchangeable".

Pikachu2000 · October 31, 2011

I mean you have to use the same extension for the whole process. You can't use mysqli_connect, then mysql_query with it. Basically, pick either mysql* or mysqli* and stick with it.

dmirsch · November 23, 2011

OK, I have three issues -- one is the same thing I have not yet been able to resolve: I need to list out EventTitle then have a sub-listing of each performance. Also for some reason I get "free event" for all EventTitles instead of for only those that are free. Lastly, my "at [time]" is not displaying. Here is my code:

<?php
  @$DB = mysqli_connect('server','login','password','database');
  if (mysqli_connect_errno()){
  	echo 'Cannot connect to database: ' . mysqli_connect_error();
   } else {
   $TheatreQuery = "SELECT Performance.PerfType, Performance.startDateTime, Events.EventTitle,  Events.thumb,Events.ShoWareEventLink, If(Events.startDATE<Now()-INTERVAL 1 DAY,'over','running') AS enddateOver, Events.Theatre, Events.FreeEvents, Promoters.Presenter, Promoters.website, venues.VenueName, venues.VenueSite, venues.Ranking FROM ((Events LEFT JOIN Performance ON Events.EventID = Performance.EventID) LEFT JOIN Promoters ON Events.PromoterCode = Promoters.PromoterCode) LEFT JOIN venues ON Performance.VenueCode = venues.VenueCode WHERE Events.Theatre=TRUE AND Performance.PerfType='Public Event' AND Events.endDATE>Now()-INTERVAL 1 DAY AND Events.endDATE<=Now()+INTERVAL 31 DAY AND Performance.startDateTime>=Now()-INTERVAL 1 DAY ORDER BY Events.EventTitle, Performance.startDateTime";
   $TheatreResult = mysqli_query($DB,$TheatreQuery);
   $TheatreNumResults = mysqli_num_rows($TheatreResult);

<p bgcolor="#BDA45E" class="categoryHeader">Theatre</p><?php
while ($Row = mysqli_fetch_assoc($TheatreResult)){
if ($Row['Theatre']==TRUE){ //THEATER
echo '<p>';
if($Row['Ranking']<10){
	echo '<img src="http://www.myalaskacenter.com/images/logos/acpa/weblogo-small_nowords.jpg" alt="Alaska Center for the Performing Arts" width="80" height="80" border="0" align="right">';
}
if ($Row['FreeEvents']=TRUE){
	echo '<img src="http://www.myalaskacenter.com/images/free.gif" alt="Free Event" width="67" height="64" align="right" border="0">';
}
echo '<a href="' . $Row['ShoWareEventLink'] . '"><img src="https://alaskapac.centertix.net/UPLImage/' . $Row['thumb'] . '" alt="' . $Row['EventTitle'] . '" title="' . $Row['EventTitle'] . '" align="left" border="0" style="padding-right:5px"><span class="Heading3_blue"><br />' . $Row['EventTitle'] . '</span>';
echo '<br />Presented by <a href="' . $Row['website'] .'" target="_blank">' . $Row['Presenter']  . '</a><br />';
echo date("l, F j, Y", strtotime($Row['startDateTime']). ' at ' . date("g:i a", strtotime($Row['startDateTime']))).' - <a href="'.$Row['VenueSite'].'" target="_blank">'.$Row['VenueName'].'</a>';
echo '<br /><br /></p><hr>';
}}

    mysqli_free_result($TheatreResult);
    mysqli_close($DB);
}
?>

Please help! If you would like to see my working webpage you can find it at http://www.myalaskacenter.com/centermail/_upcomingevents.php

PFMaBiSmAd · November 23, 2011

I need to list out EventTitle then have a sub-listing of each performance.

Someone (me) posted a link to a thread showing how to do that (you remember the title and output a new heading when the title changes.)

I get "free event" for all EventTitles

An equal comparison is two == (an assignment is one = sign) You have an if() statement using one = sign, instead of two ==.

my "at [time]" is not displaying

What format is your startDateTime value?

dmirsch · November 23, 2011

The startDateTime is formatted datetime (2011-11-25 19:00:00).

The double equals does not seem to work for the FreeEvents item -- it lists every performance as a Free Event even if the value is FALSE.

I will try the suggestion for the sub-listing that you gave. It has been a while since I posted this and when I read your response I was stuck on "Category" but now that I reread it I think I can apply what you suggested for the EventTitle.

Thanks for your quick reply PFMaBiSmAd...hopefully these answers will help you help me better!

dmirsch · November 24, 2011

Here's how I tried the coding:

<p class="categoryHeader">Theatre</p><?php
$last_eventtitle = NULL; // remember the last EventTitle
while ($Row = mysqli_fetch_assoc($TheatreResult)){
$eventtitle = $Row['EventTitle'];
if($last_eventtitle != $eventtitle){ // a new or the first EventTitle was found
	echo '<p>';
	if($Row['Ranking']<10){
		echo '<img src="http://www.myalaskacenter.com/images/logos/acpa/weblogo-small_nowords.jpg" alt="Alaska Center for the Performing Arts" width="80" height="80" border="0" align="right">';
	}
	if ($Row['FreeEvents']==TRUE){
		echo '<img src="http://www.myalaskacenter.com/images/free.gif" alt="Free Event" width="67" height="64" align="right" border="0">';
	}
	echo '<a href="' . $Row['ShoWareEventLink'] . '"><img src="https://alaskapac.centertix.net/UPLImage/' . $Row['thumb'] . '" alt="' . $Row['EventTitle'] . '" title="' . $Row['EventTitle'] . '" align="left" border="0" style="padding-right:5px"><span class="Heading3_blue"><br />' . $Row['EventTitle'] . '</span>';
	echo '<br />Presented by <a href="' . $Row['website'] .'" target="_blank">' . $Row['Presenter']  . '</a><br />';
		if($last_eventtitle != NULL){ // not the first EventTitle, close out the previous section
				echo '<br />'.date("l, F j, Y", strtotime($Row['startDateTime']). ' at ' . date("g:i a", strtotime($Row['startDateTime']))).' - <a href="'.$Row['VenueSite'].'" target="_blank">'.$Row['VenueName'].'</a>';
			}
			$last_eventtitle = $eventtitle; // save the new EventTitle
			// output the heading/start a new section
				echo '<br />'.date("l, F j, Y", strtotime($Row['startDateTime']). ' at ' . date("g:i a", strtotime($Row['startDateTime']))).' - <a href="'.$Row['VenueSite'].'" target="_blank">'.$Row['VenueName'].'</a>';
				echo '<br /><br /></p><hr>';
		}
}
?>

Now it does not list the performances correctly; although it DOES list just one event regardless of the number of performances -- so almost there. See webpage http://www.myalaskacenter.com/centermail/_upcomingevents.php. For example, Anchorage Youth Symphony only has one performance, but it lists 2; while The Nutcracker Ballet has six performances, but it lists only 2. And since I am still not getting the times in there, I'm not sure if it's listing the same performance twice or not. What am I doing wrong still?

PFMaBiSmAd · November 25, 2011

Ummm. That's not equivalent to the logic that was given.

fenway · November 25, 2011

Also, we're now down to a pure php logic issue, not MySQL.

dmirsch · November 26, 2011

OK, I'll move this to PHP Forum then.

dmirsch · November 26, 2011

moved to PHP Forum: http://www.phpfreaks.com/forums/index.php?topic=348604.0

Sign In

Create an Array from a MySQL database

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information