[RESOLVED] PHP not finding all rows

This site is best viewed in a modern browser with JavaScript enabled.

Dragonfire2008

I am trying to pull all rows from a mysql database where their date = $date

But it is not pulling all of them, I have 3 in database, it pulled 2, then I added more to make 5, and it pulled 3. I don't get it.

Here is my code (may not be perfect but it works...)

while ($row = mysql_fetch_assoc($query)) {
						echo "<p id=\"$pid\">&nbsp;</p>\n";
						echo "<div class=\"wordDef\">\n";
						echo "<div class=\"gototop\">\n";
						echo "<p onClick=\"window.location='audio.php#top';\">^ top ^</p></div>\n";
  						echo "<h4 class=\"audioServiceTitle\">";
						switch($month){
							case 1: echo "January";
							break;
							case 2: echo "February";
							break;
							case 3: echo "March";
							break;
							case 4: echo "April";
							break;
							case 5: echo "May";
							break;
							case 6: echo "June";
							break;
							case 7: echo "July";
							break;
							case 8: echo "August";
							break;
							case 9: echo "September";
							break;
							case 10: echo "October";
							break;
							case 11: echo "November";
							break;
							case 12: echo "December";
							break;
						}
						echo " $day, $year4</h4>\n";
						echo $row['name']."\n";
						echo $filename."_".$row['type']."\n";

				} // END WHILE
			if($count>0){
			mysql_data_seek($query,0);
			}

The mysql_data_seek doesn't seem to affect it either because I have other dates with rows, and it doesn't pull those at all.
There is code that is not shown, if you need that, let me know.

Thanks.

NogDog

Do a mysql_num_rows() and error_log() or echo the results so you can find out how many rows were returned by the query. Then you'll know whether you need to debug your SQL or not, first.

Dragonfire2008

I did do a debug before and num_rows came back correctly, that's why I'm confused...

I have 3 rows in database
echo $count says 3
fetch_assoc only displays 2
...argh

rtrethewey

One thing caught my eye. You pass $query to mysql_fetch_assoc(). It's impossible to know without seeing all of the code, but most code examples use the variable $result for the results of the call to mysql_query() which is then passed to mysql_fetch_assoc(). Could you have mixed these up?

Dragonfire2008

I use the $sql for the sql statement to pass into mysql_query and $query to use on everything else.

NogDog

Maybe throw in a "default:" at the end of the switch() to see if anything is slipping through there?

Dragonfire2008

Ok, i managed to get around this problem using a for loop dependent on the $count number (since that part was working), and putting $row = mysqli_fetch_row at the beginning of the loop.

Now I have another stop in my path (let me know if it would be better to create a new topic for this one)

I have everything in working order, now the thing is I would like to reverse the order of the rows fetched, starting with the last and ending with the first.

So instead of getting Jan 23, Jan 24, Jan 25, etc., it would be reversed to show the latest date first. I have tried to make this work, but seem to be getting no change, this is now my code, I have added a mysqli_data_seek($query, $rcount) to the beginning of the reversed loop hoping to reverse the row order, but it doesn't seem to change it.

// START INDIVIDUAL WEEKS

		for($day=1;$day<=$days_in_month;$day++){
			$date = date("Y-m-d", mktime(0,0,0,$month,$day,$year4));
			$sql = "SELECT * FROM `audio_recordings` WHERE `date` = '".$date."'";
			$query = mysqli_query($connection,$sql);
			$count = mysqli_num_rows($query);
			echo $count;
			if($count>0){
				if($day < 10){
					$day = "0".$day;
			}
			//for($rcount=1;$rcount<=$count;$rcount++) {
			for($rcount=($count-1);$rcount>=0;$rcount--) {
					mysqli_data_seek($query, $rcount);
					$row = mysqli_fetch_row($query);
					// $row 0 = id
					// $row 1 = name
					// $row 2 = date
					// $row 3 = type
					// $row 4 = speaker

					// convert mysql date to custom date
					$filename = explode("-",$row[2]);
					$file_year = $filename[0];
					$file_month = $filename[1];
					$file_day = $filename[2];
					$filename = $file_month."-".$file_day."-".$file_year;

					$pid = $file_month.$file_day.substr($file_year,2);

					echo "<p id=\"$pid\">&nbsp;</p>\n";
					echo "<div class=\"wordDef\">\n";
					echo "<div class=\"gototop\">\n";
					echo "<p onClick=\"window.location='audio.php#top';\">^ top ^</p></div>\n";
					echo "<h4 class=\"audioServiceTitle\">";
					switch($month){
						case 1: echo "January";
						break;
						case 2: echo "February";
						break;
						case 3: echo "March";
						break;
						case 4: echo "April";
						break;
						case 5: echo "May";
						break;
						case 6: echo "June";
						break;
						case 7: echo "July";
						break;
						case 8: echo "August";
						break;
						case 9: echo "September";
						break;
						case 10: echo "October";
						break;
						case 11: echo "November";
						break;
						case 12: echo "December";
						break;
						default: echo "Month not found.";
						break;
					}
					echo " $day, $year4</h4>\n";
					echo $row[1]."\n";
					echo $filename."_".$row[3]."\n";

				} // END FOR

			} // END IF COUNT
			echo mysqli_error($connection);
		} // END INDIVIDUAL WEEKS

NogDog

How about simply adding an ORDER BY clause to your query?

Dragonfire2008

I tried that as well, but the order is actually being done by the for loop. If it was a while($row = mysqli_fetch_assoc($query)) function, then yeah an ORDER BY would have worked...wait a sec, I think I found my problem...yup I got it now, I was reversing the wrong loop.

for($day=$days_in_month;$day>=1;$day--){

there, this matter is solved! Thanks everyone for your input