mysqli_fetch_assoc only returning 1 row from the database table

General PHP Help
#1

This code is only returning one row from the categories table

Here is what the table looks like:

id category_title category_description
1 title1 description 1
2 title2 description 2
3 title 3 description 1
4 title 4 description 1

But only the 3rd row is being shown

[php]<?php

				//Select all from the categories table and order by the category title in ascending order (ASC)
				$sql = "SELECT * FROM categories";
				
				//$res = the result of the above query or die if error
				$result = mysqli_query($DBconnect, $sql) or die (mysqli_error());
				
				//If the result has more than 0 rows
				if (mysqli_num_rows($result) > 0) {
					
					//then while $row = what has been fetched from the database
					while ($row = mysqli_fetch_assoc($result)) {
						$id = $row['id'];
						$title = $row['category_title'];
						$description = $row['category_description'];
						
						//Makes link the titles of the category and the description
						$categories = "<a href='#'>".$title."  ".$description."</a>";
					}
					echo $categories;
					
				} else {
					echo "No table found";
				}[/php]
#2

try putting the echo $categories; inside the while loop…

Plus it think if (mysqli_num_rows($result) > 0) { statement is redundant but I not sure for I use PDO.

#3

I think we are missing a concatenation on this one:

[php]

$categories .= “”.$title." “.$description.”"; //concatenation on this line

[/php]

If that is added the current code will work.

#4

[member=57087]Strider64[/member] is right, that is the problem. You are not in the loop.

#5

No, the echo is outside the loop. It will only return the last row of the DB. That line should not be concatenated. There is no starting $categories to concatenate to.

#6

;D Fair comment Kevin, so declaration of the $categories variable before the loop would then render the concatenation valid!

#7

There’s absolutely no point to that. In fact $categories should just be replaced with echo. There’s no need to even create that variable in the first place. The only reason to create a variable in the while loop is if you are putting the results in an array which the op clearly was not doing.

#8

Yes, now that I agree with totally!

Declaring variables which are unneeded can chew up memory. I was just about to post your observation myself, and if we are nit picking $id is not used either and can be removed in the current code.

#9

While being used, title and description variables do not need to be created. You can just use the row variable.

{$row [‘category_title’]}

Its not nit picking. Its just writing clean code without creating unnecessary variables

#10
Its not nit picking. Its just writing clean code without creating unnecessary variables

I agree, I wonder how often I have created unnecessary variables. The only other observation I have then I and then I am done on this thread is that selecting all the fields from a tables is also unnecessary. So “select * from table” could cost you if you only need 3 fields from the table but it has 20 columns.

I also recommend the curly braces way of escaping variables when outputting text, I started using it years ago when dealing with arrays and objects and have rarely found myself getting stuck on syntax errors.

[php]
echo “The row id is {$row[“id”]} and the category title is {$row[“category_title”]} and some other variable is {$someothervariable}”;
[/php]

Thanks Kevin

#11

Good point on select *. It is not often that you are going to need every single column in a SELECT statement. Much better to select the specific columns that you need. Additionally, anybody reading the code will know what the columns are that are currently available to the code. If you don’t know the database structure you have no idea what * contains.

Also, by using the row variables instead of creating new variables, you know clearly the value came from the database. Using the brackets to escape the row variables is also much cleaner and much less prone to making quote/concatenation errors.

  • on your last variable in your example there is no need for the brackets in that case.
#12
Using the brackets to escape the row variables is also much cleaner and much less prone to making quote/concatenation errors.

And actually faster as well. A previous company ran benchmark standards and found that concatenation can slow processing up to 70%.

Sponsor our Newsletter | Privacy Policy | Terms of Service