help with query

[rvdveen27]

Hello all,

 

A friend made this query for me in order to get a part of my page working 

			$queryCategory = "SELECT *,(SELECT dc.id FROM drive_cargotype dc, drive_routes dr WHERE dc.id = dr.cargotype AND dr.id=? ORDER BY dr.id DESC LIMIT 1) as cargo FROM drive_cargotype";
			
			$stmt = $db->prepare($queryCategory);
			$stmt->execute(array($_GET['id']));
			
			$categories = $stmt->fetchAll();

However, he didn't give me an explanation on how this query actually works. With my knowledge I understand some parts of it, but I don't know where to adapt the query so that it's also applicable for other sections of the page.

 

Is there anyone who can help me put the above query to something I'm more used to? (Example below)

 

(This query has nothing to do with previous query, it just shows how I'm used to doing queries and how I can actually understand it)

				SELECT 
					dr.id
					,u.username as 'driver'
					,sloc.name as 'start'
					,scom.name as 'startcompany'
					,eloc.name as 'end' 
					,ecom.name as 'endcompany'
					,cargoweight
					,dc.name as 'cargo'
					,dct.name as 'cargotype'
					,time
					,cargodamage
					,drt.name as 'rating'
					,distance
					,price 
					,costs
					,screenshot
					,status
					,price - costs - cargodamage as 'profit'
				FROM drive_routes dr
				INNER JOIN
					users u ON u.id = dr.driver
				LEFT JOIN
					users hb ON hb.id = dr.handledby
				INNER JOIN
					drive_locations sloc ON sloc.id = dr.start
				INNER JOIN
					drive_locations eloc ON eloc.id = dr.end
				INNER JOIN
					drive_companies scom ON scom.id = dr.startcompany
				INNER JOIN
					drive_companies ecom ON ecom.id = dr.endcompany
				INNER JOIN
					drive_cargo dc ON dc.id = dr.cargo
				INNER JOIN
					drive_cargotype dct ON dct.id = dr.cargotype
				INNER JOIN
					drive_rating drt ON drt.id = dr.rating
				WHERE dr.id = ". $_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(); 

Thanks so much in advance!

 

[Barand]

Why would a friend do that to you? Have you upset him recently?

 

Stick to your familiar joins and don't introduce subqueries with inefficient join syntax (... FROM A, B WHERE ...)

[rvdveen27]

Why would a friend do that to you? Have you upset him recently?

 

Stick to your familiar joins and don't introduce subqueries with inefficient join syntax (... FROM A, B WHERE ...)

 

Lol no I did not upset him, he just has a different way of doing things. The whole idea was to create a query, that selects the correct option based on the information in the database. Which is something I now also need to apply for other selection fields. 

 

Here's the whole thing: 

			$queryCategory = "SELECT *,(SELECT dc.id FROM drive_cargotype dc, drive_routes dr WHERE dc.id = dr.cargotype AND dr.id=? ORDER BY dr.id DESC LIMIT 1) as cargo FROM drive_cargotype";
			
			$stmt = $db->prepare($queryCategory);
			$stmt->execute(array($_GET['id']));
			
			$categories = $stmt->fetchAll();

				Cargo type:<br />
				<select name="cargotype" class="form-control">
					<?php foreach($categories as $category): ?>
						<option value="<?=$category['id']?>" 
						<?php if($category['cargo'] == $category['id']){echo "selected";}?>> 
						<?=$category['name']?><option> 
					 <?php endforeach; ?>

[rvdveen27]

So I had my own try at this query. This is how far I got: 

			$query = " 
				SELECT 
					dr.id as 'id',
					name
				FROM drive_rating dr
				LEFT JOIN
					drive_routes dro ON dro.rating = dr.id
				WHERE dr.id=?
				
				"; 
				try 
				{ 
					$stmt = $db->prepare($query);
					$result = $stmt->execute(array($_GET['id'])); 
				} 
				catch(PDOException $ex) 
				{ 
					die("Failed to run query: " . $ex->getMessage()); 
				}
				 
			$results4 = $stmt->fetchAll(); 

The idea that it gets all the "id" and "name" from the table "drive_ratings" and that it NEXT to that, also check which "id" in the table "drive_ratings" is equal to the "rating" in the table "drive_routes". I feel I'm only missing like one critical thing here but I can't put my finger on it.

 

Anyone has any idea how to complete this query? 

[Psycho]

Your explanation isn't very clear. It might help to show some sample data in those two tables and the expected results from the query.

[Barand]

Your attempt doesn't use the same tables

 

SELECT
                    dr.id as 'id',
                    name
                FROM drive_rating dr
                LEFT JOIN
                    drive_routes dro ON dro.rating = dr.id
                WHERE dr.id=?

 

compared with

 

SELECT *,(SELECT dc.id FROM drive_cargotype dc, drive_routes dr WHERE dc.id = dr.cargotype AND dr.id=? ORDER BY dr.id DESC LIMIT 1) as cargo FROM drive_cargotype";

[rvdveen27]

Correct. That's because my attempt is for the field "drive rating" in the form. The other query was for the field "cargo type" in the form.

 

Here's extra clarification.

 

Below is one row of data pulled from the database.

 

 

The image below this, is the page that should fill the <form> fields with the information that is in the database as stated above.

 

 

The blue field is the field that actually goes correctly. The <option> selected in the <form> on this page, is correct info from the database.

The red fields are fields that I still need to fix with a (similar) query. Currently it just selects the first <option> in the list. I want it to select ( "selected") the same value as the one that's said in the database (the row of data I provided). 

Edited August 25, 2015 by rvdveen27

[rvdveen27]

First image provided was incorrectly, here's the correct one: 

 

You can see that the blue fields match in both pictures (thanks to that query), while the red fields do not and should be matching.

[rvdveen27]

More experimenting with my query gets me the following.
 

				SELECT 
					dr.id as 'id',
					name,
					dro.rating
				FROM drive_rating dr
				LEFT JOIN
					drive_routes dro ON dro.rating = dr.id

Which results in: 

 

And when I add the WHERE to the query: 

				SELECT 
					dr.id as 'id',
					name,
					dro.rating
				FROM drive_rating dr
				LEFT JOIN
					drive_routes dro ON dro.rating = dr.id
				WHERE dro.id = 539

It results in:

 

It's slowly getting there. But it's still not what I want it to do. Anyone has any idea how to get this right? 

 

Again. I want it to get all the entries from "id" and "name" from the table "drive_ratings" while also checking which "id" in the table "drive_ratings" matches the "rating" in the table "drive_routes".