Jump to content

Issue reading from two tables

Ben2

By Ben2
in PHP Coding Help

 Share

Recommended Posts

Ben2 Newbie

Ben2

Posted
Posted

Hey guys and girls,

 

I am rather new to php and I have ran into a problem with my code that I hope someone can help me with.

 

I have tried multiple things to get this to work but I never get the result I am after. I either end up with only one lot of information being displayed or with information for every member being displayed.

 

I am able to get all the information I need from the first database table and display it but after completing that task I decided that I wanted to show the "nickname" of the owner of the image. To do this I need to take the information from the "owner" column from table one and link it to the "ID" column of table two.

 

If anyone can help I will be very very greatful as it has been annoying me for a couple of hours now!

 

The code I have so far is:

(Please note I have replaced some information with "*****" for obvious reasons)

 

<?php

mysql_connect("localhost", "*******", "*****") or die(mysql_error());
mysql_select_db("********") or die(mysql_error());

$dir = "*****************";	

$query = mysql_query("SELECT * FROM `***********`") or die(mysql_error());  
$query2 = mysql_query("SELECT * FROM `*********`") or die(mysql_error()); 

while($row = mysql_fetch_array($query)) {

$id = $row[iD];
$title = $row[Title];
$uri = $row[uri];
$owner = $row[Owner];
$views = $row[Views];
$featured = $row[Featured];

}	
while($secondrow = mysql_fetch_array($query2)) {

$id2 = $secondrow[iD];
$nickname = $secondrow[NickName];

}

if($featured == "1"){
if ($id2 == $owner){
	echo '<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
		<html xmlns="http://www.w3.org/1999/xhtml">
		<head>	
		<meta http-equiv="Content-Type" content="text/html; charset=utf-8" /><style type="text/css">';

	echo '.centering {display: block; margin-left: auto; margin-right: auto; margin-top:10px; }';

	echo '</style></head><body>';

	echo '<div style="margin-left:auto; margin-right:auto; width:170px; background-color:#fff; padding:10px;"><div class="boxContent"><div class="dbContent">
<a href="m/videos/view/'.$uri.'"><img src="'.$dir.''.$id.'.jpg" width="140px" height="110px" style="border:6px double #545565;"/></div></a><br /><p><div class="sys_file_search_title" style="margin-top:-15px;"><a href="m/videos/view/'.$uri.'">'.$title.'</a><br /></div><div class="sys_file_search_from" style="margin-top:0px;">From <a href="'.$nickname.'">'.$nickname.'</a><br /></div><div class="sys_file_search_when" style="margin-top:0px;">'.$views.' Views</p></div></div></div>';
}
}
?>

Link to comment
Share on other sites
fortnox007 Regular Member

fortnox007

Posted
Posted

maybe have a look at this :

http://www.w3schools.com/Sql/sql_join.asp

 

that way you only need to query ones :)

Hope it helps a bit, if not let me know

 

those *** which are for obvious(?) reasons don't let us see your table names ofcourse : ) Which for an JOIN would be great (i do understand the *** at your connect and mysql_select_db

Link to comment
Share on other sites
Ben2 Newbie

Ben2

Posted
  • Author
Posted

maybe have a look at this :

http://www.w3schools.com/Sql/sql_join.asp

 

that way you only need to query ones :)

Hope it helps a bit, if not let me know

 

those *** which are for obvious(?) reasons don't let us see your table names ofcourse : ) Which for an JOIN would be great (i do understand the *** at your connect and mysql_select_db

 

Thanks for the link,

 

I guess this is the kind of thing that I need... now I just have to figure out how to use it! lol

 

I was hoping it would have been an easy solve such as a simple if statement that was something like:

 

IF "featured" in table 1 = 1 then match "owner" in table 1 with "ID" in table 2 and get the "nickname" from table 2 of the row that  matches

 

but if my only real choice is to use what is described in the link you supplied I guess I will have to rewrite most of my code and figure out how to use it lol

 

Thanks for the advice. I guess its back to the drawing board to rethink things!

 

Link to comment
Share on other sites
fortnox007 Regular Member

fortnox007

Posted
Posted

Really try the SQL, and certainly also look in SQL date functions, SQL has some cool stuff, which you otherwise have to do in php with much more code and multiple queries. The tutorial I gave is a great website to get a jump start, without to much of reading on most programming languages and mark-up languages. I learned most of my stuff from them. And bought a some book afterwards. (besides hanging around here  ::))

And besides that there is no use for inefficient code. I need to rewrite my stuff al the time, but I like it because I know it will be better. And ones you have a nice client for a lot of customers all they want you to do is deliver the best possible stuff. And yes you can do it with if-statements i am pretty sure :)

 

Oh and if you still wanna do it with if-statements (worth looking in)

check:  http://stackoverflow.com/questions/2492327/how-do-i-combine-two-arrays-in-php-based-on-a-common-key

 

I am pretty sure you want the sql after reading that : )

Link to comment
Share on other sites
Ben2 Newbie

Ben2

Posted
  • Author
Posted

Thanks for your advice.

 

I have looked into using the SQL JOIN stuff but I am having trouble with it.

 

I put the SQL JOIN stuff into phpmyadmin and the results it show are what I wanted (the important information from both tables put into one) so I click on the "Create php code" button and copied the code given into mine and modified it to how I think it would work, however it does not work lol

 

Please could someone point me in the right direction as I am a bit stuck with this.

 

I have modified my code as show below:

 

mysql_connect("localhost", "*****", "*****") or die(mysql_error());
mysql_select_db("*****") or die(mysql_error());

$dir = "*****/*****/*****/";	

$sql = "SELECT RayVideoFiles.ID, RayVideoFiles.Owner, RayVideoFiles.Views, RayVideoFiles.Featured, RayVideoFiles.Uri, RayVideoFiles.Title, Profiles.ID, Profiles.NickName\n"
    . "FROM RayVideoFiles\n"
    . "INNER JOIN Profiles\n"
    . "ON RayVideoFiles.Owner=Profiles.ID\n"
    . "ORDER BY Profiles.ID LIMIT 0, 30 ";


while($row = mysql_fetch_array($sql)) {
	echo '<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
		<html xmlns="http://www.w3.org/1999/xhtml">
		<head>	
		<meta http-equiv="Content-Type" content="text/html; charset=utf-8" /><style type="text/css">';

	echo '.centering {display: block; margin-left: auto; margin-right: auto; margin-top:10px; }';

	echo '</style></head><body>';

	echo '<div style="margin-left:auto; margin-right:auto; width:170px; background-color:#fff; padding:10px;"><div class="boxContent"><div class="dbContent">
<a href="m/videos/view/'.$row[uri].'"><img src="'.$dir.''.$row[iD].'.jpg" width="140px" height="110px" style="border:6px double #545565;"/></div></a><br /><p><div class="sys_file_search_title" style="margin-top:-15px;"><a href="m/videos/view/'.$row[uri].'">'.$row[Title].'</a><br /></div><div class="sys_file_search_from" style="margin-top:0px;">From <a href="'.$row[NickName].'">'.$row[NickName].'</a><br /></div><div class="sys_file_search_when" style="margin-top:0px;">'.$row[Views].' Views</p></div></div></div>';
}

Link to comment
Share on other sites
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.