PHP OOP PDO MySQL Select from two tables

[spertuit]

I'm attempting to select all records from a table than loop through the results and select all records from another table where tableid is equal to the id of the results of the first query.

So basically select all from first table, while you have results, select all from second table where tableid is equal to first results current id, than pass all this into an array for use later.

I'll take a stab at it, but I'm trying to get properly written code in OOP style using PDO. Its basically a one to many relationship, 1 record in table1 will have multiple records in table2. 

public function showLog($table){
		
		$sql="SELECT * FROM $table";
		$q= $this->conn->query($sql) or die("failed!");
		
		while($r = $q->fetch(PDO::FETCH_ASSOC)){
			$sql="SELECT * FROM :table WHERE id = :id";
			$q = $this->conn->prepare($sql);
			$q->execute(array(':table'=>"vessels",':id'=>$id));
			$data = $q->fetch(PDO::FETCH_ASSOC);
		}
		return $data;	
	}

Edited May 10, 2013 by spertuit

[Jessica]

No! Bad!

 

Use a join.

[spertuit]

im not sure if a join will work. lets try simpler for now.

 

logs               vessels

------              ---------

id                       id

date                 name

vesselID

 

now i want to display a table that will show many logs that display logs.id,logs.date,vessels.name where vesselID = vessels.id

and I'm trying to do it using PDO 

 

I'm close

		public function showLog($table){
		
		$sql="SELECT * from $table";
		$q= $this->conn->query($sql) or die("failed!");
		while($r = $q->fetch(PDO::FETCH_ASSOC)){
					$sql="SELECT * FROM 'vessels' WHERE id = :id";
					$q = $this->conn->prepare($sql);
					$q->execute(array(':id'=>$r['logVesselID']));
					$data = $q->fetch(PDO::FETCH_ASSOC);
					$data[] = $r;
		}
		return $data;
			
	}

Edited May 10, 2013 by spertuit

[requinix]

Stop doing that.

 

Use a join. It will work.

[spertuit]

You guys are tough

		public function showLog($table){
		
		$sql="SELECT * FROM `dailyMasterLogs` AS q LEFT JOIN `vessels` AS i ON ( q.logVesselID = i.id)";
		$q= $this->conn->query($sql) or die("failed!");
		while($r = $q->fetch(PDO::FETCH_ASSOC)){
					$data[]=$r;
		}
		return $data;

Can you get me advice to clean this up or does it look ok? It works, I'm just trying to get it the best I can.

[Jessica]

Did it work?

 

(You should pick aliases that mean something.

 

Probably also add in an ORDER BY dailyMasterLogs id, so you can output them easily. For more on that see my tutorial here: http://thewebmason.com/tutorial-parent-child-lists/)

[spertuit]

		public function showLog($table){
		
		$sql="SELECT * FROM `dailyMasterLogs` AS logs LEFT JOIN `vessels` AS vessel ON ( logs.logVesselID = vessel.id) ORDER BY logs.id DESC";
		$q= $this->conn->query($sql) or die("failed!");
		while($r = $q->fetch(PDO::FETCH_ASSOC)){
					$data[]=$r;
		}
		return $data;

Here is the final. I appreciate the help, and I'll check out that tutorial.