Only one row shown in table

rvdveen27 · July 5, 2015

Hello all.

I'm trying to show the last 5 jobs done by an user on their profile. Now currently as my code stands, it's full of incorrect fields & data, but what matters at this point is that I only get one row shown in the table. I've tried several things but for some reason it keeps on refusing showing multiple rows in the table. The query is identical to the query on another page and on that page it shows multiple rows in the table.

I'm hoping anyone can find the problem here because I can't find what's causing it, thanks in advance:

Here is the code I'm talking about:

<?php
							$query = " 
        SELECT 
			id,
			start, 
			startcompany, 
			end, 
			endcompany, 
			cargoweight,
			cargo,
			cargotype,
			time,
			cargodamage,
			rating,
			distance, 
			price, 
			costs,
			screenshot,
			status
        FROM drive_routes
			WHERE driver = ". $_GET['id'] ."
		"; 
        try 
        { 
            $stmt = $db->prepare($query); 
            $result = $stmt->execute(); 
        } 
        catch(PDOException $ex) 
        { 
            die("Failed to run query: " . $ex->getMessage()); 
        }
		
	$rows = $stmt->fetchAll(); 
	$count = $stmt->rowcount();
						?>
						
						<h3>Driving Information</h3>
						<div class="well well-sm" style="width: 500px;">
							<b>Total Accepted Deliveries: </b> <?php echo $countroutes; ?></br>
							<b>Total Kilometres Driven: </b> <?php echo $total; ?></br>
							<b>Total Money Earned: </b> €<?php echo $totalmoney; ?></br>
						</div>
						<div class="table-responsive">
							<table class="table table-striped">
								<thead>
								  <tr>
									<th>#</th>
									<th>Starting position</th>	 
									<th>Ending position</th>
									<th>Cargo</th>
									<th>Expenses</th>
									<th>Rating</th>
									<th>Distance</th>
									<th>Time Taken</th>
									<th>Cargo Damage</th>
									<th>Profit</th>
								  </tr>
								</thead>
							
							
								<tbody>
								<?php foreach($rows3 as $row){
						
						// Row2 starting position
									$query = " 
										SELECT 
											name
										FROM drive_locations
									"; 
									$query_params = array( 
										':id' => $row['start'] 
									); 
									$query .= " 
										WHERE 
											id = :id 
									"; 
									try 
									{ 
									$stmt = $db->prepare($query); 
									$result = $stmt->execute($query_params); 
									} 
									catch(PDOException $ex) 
									{ 
									die("Failed to run query: " . $ex->getMessage()); 
									}
									$row2 = $stmt->fetch();
									
									// Row4 ending position
				$query = " 
					SELECT 
						name
					FROM drive_locations
				"; 
				$query_params = array( 
					':id' => $row['end'] 
				); 
				$query .= " 
					WHERE 
						id = :id 
				"; 
				try 
				{ 
					$stmt = $db->prepare($query); 
					$result = $stmt->execute($query_params); 
				} 
				catch(PDOException $ex) 
				{ 
					die("Failed to run query: " . $ex->getMessage()); 
				}
				$row4 = $stmt->fetch();
				
				// Row6 cargo
				$query = " 
					SELECT 
						name
					FROM drive_cargo
		        "; 
				$query_params = array( 
					':id' => $row['cargo'] 
				); 
				$query .= " 
				WHERE 
					id = :id 
				"; 
				try 
				{ 
					$stmt = $db->prepare($query); 
					$result = $stmt->execute($query_params); 
				} 
				catch(PDOException $ex) 
				{ 
					die("Failed to run query: " . $ex->getMessage()); 
				}
				$row6 = $stmt->fetch();
				
				// Row8 rating
				$query = " 
					SELECT 
						name
					FROM drive_rating
		        "; 
				$query_params = array( 
					':id' => $row['rating'] 
				); 
				$query .= " 
					WHERE 
						id = :id 
				"; 
				try 
				{ 
					$stmt = $db->prepare($query); 
					$result = $stmt->execute($query_params); 
				} 
				catch(PDOException $ex) 
				{ 
					die("Failed to run query: " . $ex->getMessage()); 
				}
				$row8 = $stmt->fetch();

				// Row9 profit
				$query = " 
					SELECT 
						price, 
						costs, 
						cargodamage, 
						price - costs - cargodamage as profit
					FROM drive_routes
				";
				$query_params = array( 
					':id' => $row['id'] 
				); 
				$query .= " 
					WHERE 
						id = :id 
				";
				try 
				{ 
					$stmt = $db->prepare($query); 
					$result = $stmt->execute($query_params); 
				} 
				catch(PDOException $ex) 
				{ 
					die("Failed to run query: " . $ex->getMessage()); 
				}
				$row9 = $stmt->fetch();
						
						}
?>						
								  <tr>
									<td><?php echo $row['id']; ?></td>
									<td><?php echo htmlentities($row2['name'], ENT_QUOTES, 'UTF-8'); ?></td>
									<td><?php echo htmlentities($row4['name'], ENT_QUOTES, 'UTF-8'); ?></td>
									<td><?php echo htmlentities($row6['name'], ENT_QUOTES, 'UTF-8'); ?></td>
									<td>€<?php echo htmlentities($row['costs'], ENT_QUOTES, 'UTF-8'); ?></td>
									<td><?php echo htmlentities($row8['name'], ENT_QUOTES, 'UTF-8'); ?></td>
									<td><?php echo htmlentities($row['distance'], ENT_QUOTES, 'UTF-8'); ?> KM</td>
									<td><?php echo htmlentities($row['time'], ENT_QUOTES, 'UTF-8'); ?></td>
									<td>€<?php echo htmlentities($row['cargodamage'], ENT_QUOTES, 'UTF-8'); ?></td>
									<td>€<?php echo htmlentities($row9['profit'], ENT_QUOTES, 'UTF-8'); ?></td>
								  </tr>

Barand · July 5, 2015

Looking at that code one would think you had never heard of JOINS.

However, I know differently.

http://forums.phpfreaks.com/topic/296174-trying-to-get-a-several-rows-and-colums-with-data/?do=findComment&comment=1511474

Not open to advice are you?

rvdveen27 · July 5, 2015

Looking at that code one would think you had never heard of JOINS.

However, I know differently.

http://forums.phpfreaks.com/topic/296174-trying-to-get-a-several-rows-and-colums-with-data/?do=findComment&comment=1511474

Not open to advice are you?

I am sorry if you feel insulted Barand. The reason that I'm not using a join in this query is because I haven't gotten to the point where I feel that I have enough knowledge to actually safely apply that to all other pages. It's definitely a future plan to replace all the queries with joins.

But as I said, the reason I haven't done this yet is because I doubt my own skill set and knowledge to be able to apply this to all other pages (and get it working 100% correctly). And it's not because I'm not open to advice. I hope you can understand that it's me doubting myself, rather than not willing to listen to your advice.

Now yes, I could come back here for every single mistake I make if I try to put the join queries into all the pages and things go wrong. But I already feel that I'm asking way too much of the people here and I need to get my own knowledge and skill set going. I need to try and develop myself in that way and I don't want to ask more help with that then that which I really need.

Edited July 5, 2015 by rvdveen27

jcbones · July 5, 2015

You will never "have that skill", unless you first try and make that skill. Don't doubt yourself, push through that, know that you can. Follow the syntax, and it will all come together for you. http://https://dev.mysql.com/doc/refman/5.0/en/join.html

PS. The reason you are only getting one row, is you are only asking for one row. You haven't ask for the rest from the resource.

Edited July 5, 2015 by jcbones

Barand · July 5, 2015

The only loop I can see in the code is

foreach($rows3 as $row){

Where is $rows3 defined?

rvdveen27 · July 5, 2015

The only loop I can see in the code is
foreach($rows3 as $row){
Where is $rows3 defined?

I had the main query defined as row3 earlier to make sure it wasn't messing up with queries running (for different purposes) before this query. It wasn't and I forgot to change that back.

However, I have edited my code with INNER JOINs (I really hope I did do this right). I googled the inner joins and found out that I can just keep repeating that so I hope that's right. $row3 is now also defined again. However I am still getting only one row.

						<?php
							$query = " 
								SELECT 
									dr.id
									,start
									,startcompany
									,end
									,endcompany
									,cargoweight
									,cargo
									,cargotype
									,time
									,cargodamage
									,rating
									,distance
									,price
									,costs
									,screenshot
									,status
									,SUM(price - costs - cargodamage) as 'profit'
								FROM drive_routes dr
								INNER JOIN
									drive_locations dl ON dl.id = dr.start
								INNER JOIN
									drive_cargo dc ON dc.id = dr.cargo
								INNER JOIN
									drive_rating dra ON dra.id = dr.rating
								WHERE driver = ". $_GET['id'] ."
							"; 
							try 
							{ 
								$stmt = $db->prepare($query); 
								$result = $stmt->execute(); 
							} 
							catch(PDOException $ex) 
							{ 
							die("Failed to run query: " . $ex->getMessage()); 
							}
		
							$rows3 = $stmt->fetchAll(); 
							$count = $stmt->rowcount();
						?>
						
						<h3>Driving Information</h3>
						<div class="well well-sm" style="width: 500px;">
							<b>Total Accepted Deliveries: </b> <?php echo $countroutes; ?></br>
							<b>Total Kilometres Driven: </b> <?php echo $total; ?></br>
							<b>Total Money Earned: </b> €<?php echo $totalmoney; ?></br>
						</div>
						<div class="table-responsive">
							<table class="table table-striped">
								<thead>
								  <tr>
									<th>#</th>
									<th>Starting position</th>	 
									<th>Ending position</th>
									<th>Cargo</th>
									<th>Expenses</th>
									<th>Rating</th>
									<th>Distance</th>
									<th>Time Taken</th>
									<th>Cargo Damage</th>
									<th>Profit</th>
								  </tr>
								</thead>
							
							
								<tbody>
								<?php
									foreach($rows3 as $row):
								;
								?>
								  <tr>
									<td><?php echo $row['id']; ?></td>
									<td><?php echo htmlentities($row2['name'], ENT_QUOTES, 'UTF-8'); ?></td>
									<td><?php echo htmlentities($row4['name'], ENT_QUOTES, 'UTF-8'); ?></td>
									<td><?php echo htmlentities($row6['name'], ENT_QUOTES, 'UTF-8'); ?></td>
									<td>€<?php echo htmlentities($row['costs'], ENT_QUOTES, 'UTF-8'); ?></td>
									<td><?php echo htmlentities($row8['name'], ENT_QUOTES, 'UTF-8'); ?></td>
									<td><?php echo htmlentities($row['distance'], ENT_QUOTES, 'UTF-8'); ?> KM</td>
									<td><?php echo htmlentities($row['time'], ENT_QUOTES, 'UTF-8'); ?></td>
									<td>€<?php echo htmlentities($row['cargodamage'], ENT_QUOTES, 'UTF-8'); ?></td>
									<td>€<?php echo htmlentities($row9['profit'], ENT_QUOTES, 'UTF-8'); ?></td>
								  </tr>
								<?php endforeach; ?>
								</tbody>
					</center>
				</div>
			<?php require('extra/footer.php'); ?>

Barand · July 5, 2015

This should get all the info you need with a single query instead of all those separate ones

        SELECT 
            id,
            sloc.name as start, 
            startcompany, 
            eloc.name as end, 
            endcompany, 
            cargoweight,
            dc.name as cargo,
            cargotype,
            time,
            cargodamage,
            drate.name as rating,
            distance, 
            price, 
            costs,
            screenshot,
            status
            price - costs - cargodamage as profit
        FROM drive_routes as dr
            INNER JOIN drive_locations as sloc ON dr.start = sloc.id
            INNER JOIN drive_locations as eloc ON dr.end = eloc.id
            INNER JOIN drive_cargo as dc ON dr.cargo = dc.id
            INNER JOIN drive_rating as drate ON dr.rating = drate.id
            
        WHERE driver = ". $_GET['id'] ."

rvdveen27 · July 5, 2015

I have that now, see my reply #6. Only the problem of only one row in the table remains. For which reason I still don't understand.

Some better naming done by you on the tables though, I'll change to that as that seems easier to understand.

Edited July 5, 2015 by rvdveen27

jcbones · July 5, 2015

Have you run the query in mysql console, or phpmyadmin or other interface, to see what it returns?

rvdveen27 · July 5, 2015

Same result:

rvdveen27 · July 6, 2015

Compared Barand's query to mine (reply #7), apparently the issue in the query resulting in only one row was my:

,SUM(price - costs - cargodamage) as 'profit'

Thanks again for helping!

Sign In

Only one row shown in table

Recommended Posts

rvdveen27

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

rvdveen27

Link to comment

Share on other sites

jcbones

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

rvdveen27

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

rvdveen27

Link to comment

Share on other sites

jcbones

Link to comment

Share on other sites

rvdveen27

Link to comment

Share on other sites

rvdveen27

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information