PHP - Need Help Joining Tables Fields Through A Relational Database

Hello there,

decided to try to have a joined table query, running into a couple of problems.
Here is the query:

$sql=mysql_query("SELECT * FROM `buds`, `unlocked_buds` WHERE `id`.buds = `bud_id`.unlocked_buds ORDER BY `id`.buds ASC") or die("A MySQL error has occurred.<br />Your Query: " . $sql . "<br /> Error: (" . mysql_errno() . ") " . mysql_error());



Which gives the error:
Quote

A MySQL error has occurred.
Your Query:
Error: (1054) Unknown column 'id.buds' in 'where clause'



The two tables are 'buds' and 'unlocked_buds'


Not really sure what to change, I have been following this tutorial http://www.sqltraining.org/selecting+data+from+multiple+tables/, but really is not helping that much.


What exactly does that mean? What really is happening?

I currently use the following query to get status's and count the number of likes from 2 different tables. However, I now want to join the user table to this query.

SELECT s.*, COUNT(l.likes_location_id) AS likeCount
 FROM stream AS s
  LEFT JOIN likes AS l ON ( l.likes_location_id = s.stream_id )
 GROUP BY s.stream_id
 ORDER BY s.stream_id DESC LIMIT 50

What do I need to add to include the 'user' table?   Also, could anyone recommend a good tutorial for joining tables?

Many thanks,  

i have 2 tables.

table 1
- Id
-Name
-ShortId

table 2
-lots of data....
-Category (same value with table1 Id)

im trying to join this 2 tables...

Code: [Select]
$query = "SELECT * FROM tblnews_categories WHERE ShortId != 0 ORDER BY `ShortId`";
$result = mysql_query($query);
$query2 = "SELECT * FROM tblnews_categories, tblnews WHERE tblnews_categories.Id = tblnews.Category ";
$result2 = mysql_query($query2);
while($row = mysql_fetch_assoc($result))
{

?>
<div class="quartetin2">
<div id="HomeMoreNewsIMG2"><div id="HomeMoreNewsTitle2"><a href="summary?catid=19167"><?php echo strtoupper($row['Name']);?></a></div></div>
<div id="HomeMoreNewsText">
<?php 
while($row2 = mysql_fetch_assoc($result2))
{
?>
<tr>
<td id="HomeLatestNewsDate"><li><?php echo strtoupper($row2['Title']);?></li></td>

</tr>


<?php } ?>

What i want to do is when Id = Category echo all data under the specific category

Hello, I am very new to both php and sql.

I hope this is not confusing. 
Lets say I want to join 2 tables.

When I put it through a while loop, how do I write the code so that all id's in table2 are on the SAME line for each referring id in table 1, before dropping to the next line and going to the next record in table 1?

I THINK that actually makes sense if you read it carefully :-)

This topic has been moved to MySQL Help.

http://www.phpfreaks.com/forums/index.php?topic=308889.0

Hi
As I'm new to PHP/MySQL
I need a little help concerning joining 4 tables in SQL query.
I have 4 tables
first is asort with 4+ columns
id   catid   pid   tipid   ...

id is primary key and catid, pid and tipid are foreign keys from tables kategorija, proizvod and tip

I want to make a query to join this 4 tables and find for example all rows with pid=4
I tryed with
Code: [Select]
$q1 = "SELECT * FROM asort LEFT JOIN (kategorija, proizvod, tip)
                 ON (kategorija.catid=asort.catid AND proizvod.pid=asort.pid AND tip.tipid=asort.tipid)
where tipid='$_GET[tipid]' order by cijena asc";
but I got
"Column 'tipid' in where clause is ambiguous"

Thank you for reading this especially if you are able to help me out on this.
Bye
DVAL

$sql = "SELECT * FROM leads  WHERE accesstoken = '".$_POST["userAc"]."'"; 
                $result = mysql_query($sql) or trigger_error("Query Failed: " . mysql_error()); 
 
                if(mysql_num_rows($result)>0) 
                  {
	
                      while($row = mysql_fetch_array($result))
                      {
	           
                      
	   
	                  $id= $row['id'];
					  $fullname= $row['fullname'];
					  $email= $row['email'];
					  
					  $to = $email;
					  
					    $message .=	$_POST["userMessage"];
		                $subject = $_POST["userSubject"];
						
						$headers = "From: " . $_POST["userEmail"] . "\r\n";
			            $headers .= "Reply-To: ". $_POST["userEmail"] . "\r\n";
			            $headers .= "MIME-Version: 1.0\r\n";
			            $headers .= "Content-Type: text/html; charset=ISO-8859-1\r\n";
						
						if (mail($to, $subject, $message, $headers)) 
			                    {
	
	
	
		                        $output = json_encode(array('type'=>'error', 'text' => '<p>Your Message was sent successfully!</p>'));
		
		                        die($output);
								}
								else
								{
									$output = json_encode(array('type'=>'error', 'text' => '<p>Your Message was not sent!</p>'));
		
		                             die($output);
									
								}
				     }
				  
                  }

I have another table called accounts that I want to join with this one, I need to select from ref to get the email from account table also!
  be something like this

$sql = "SELECT * FROM accounts  WHERE ref = '".$_POST["userAc"]."'"; 
                $result = mysql_query($sql) or trigger_error("Query Failed: " . mysql_error()); 

 

This topic has been moved to MySQL Help.

http://www.phpfreaks.com/forums/index.php?topic=331758.0

Hey all,

Quick sum up: I have a social website where users can follow/post/vote etc. I am adding a new facebook-like activity wall feature to each users profiles.

I have worked on this bit for a few days now and can't seem to get it to work properly. What I want is, well, exactly like the facebook wall as I mentioned...When a visitor visits a user's profile, that user's activity will display, whether they recently "voted" on a post, commented, or posted something of their own, I would like it to display on their profile and of course, have the most recent entries on top.

The problems I am having right now a it's displaying some things twice and it's not sorting them by the most recent.

I am working with 3 tables here (I only posted the columns that I am using):
Code: [Select]
Table structure for table votes_posts
Field Type Null Default
indexer int(11) No
post_id int(11) No
vote int(11) No
user_name varchar(250) No
date varchar(255) No

Table structure for table posts
Field Type Null Default
id int(11) No
name varchar(30) No
content text No
datetimerss varchar(255) No
category int(11) No

Table structure for table comments
Field Type Null Default
id int(12) No
post_id int(12) No
name varchar(30) No
content text No
type varchar(30) No
datetimerss varchar(255) No

$users is just $_GET['user']
The timeAgo() function, simply displays the very popular "posted xx ago" date time.
<ul class="streamActivity">
<?php
$checkActivity = "SELECT votes_posts.post_id, votes_posts.user_name, votes_posts.vote, votes_posts.date, 

posts.id, posts.content, posts.name, posts.datetimerss, categories.category, 

comments.post_id AS comm_postid, comments.content AS comm_content, comments.name AS comm_name, comments.datetimerss AS comm_date, comments.type 

FROM `votes_posts` 

LEFT OUTER JOIN `posts` ON votes_posts.post_id = posts.id 

LEFT OUTER JOIN `comments` ON posts.id = comments.post_id 

LEFT OUTER JOIN `categories` ON posts.category = categories.cat_id

WHERE (votes_posts.post_id = posts.id AND user_name = '$user')

OR (comments.post_id = posts.id AND comments.name = '$user')

OR (posts.name = '$user') ORDER BY votes_posts.date, posts.datetimerss, comments.datetimerss ASC";
$checkActivityResult = mysql_query($checkActivity);

if (mysql_num_rows($checkActivityResult) > 0) {

while ($sessionAct = mysql_fetch_array($checkActivityResult)) {

$category = strtolower($sessionAct['category']);

$dateVote = timeAgo($sessionAct['date']);

$datePost = timeAgo($sessionAct['datetimerss']);

$dateComm = timeAgo($sessionAct['comm_date']);

$namePost = $sessionAct['name'];

$nameComm = $sessionAct['comm_name'];

$nameVote = $sessionAct['user_name'];

$idVote = $sessionAct['post_id'];

$idPost = $sessionAct['id'];

$idComm = $sessionAct['comm_postid'];

$contentPost = $sessionAct['content'];

$contentComm = $sessionAct['comm_content'];

$typeComm = $sessionAct['type'];

if ($namePost == $user) {

$classList = 'storyPost';

$classIcon = 'iconMuttr';

$name = $namePost;

$content = $contentPost .' ... read more';

$datetime = $datePost;

}

elseif ($nameComm == $user && $typeComm == "Comment") {

$classList = 'actionPost';

$classIcon = 'iconComment';

$name = $nameComm;

$content = 'Commented "'. $contentComm .'"';

$datetime = $dateComm;

} elseif ($nameComm == $user && $typeComm == "Advice") {

$classList = 'actionPost';

$classIcon = 'iconAdvice';

$name = $nameComm;

$content = 'gave Advice "'. $contentComm .'"';

$datetime = $dateComm;

}

elseif ($nameVote == $user) {

if ($sessionAct['vote'] == "1") {

$classList = 'actionPost';

$classIcon = 'iconLaughed';

$name = $nameVote;

$content = 'Laughed at "'. $contentPost .'"';

$datetime = $dateVote;

} elseif ($sessionAct['vote'] == "2") {

$classList = 'actionPost';

$classIcon = 'iconLoved';

$name = $nameVote;

$content = 'Showed Love on "'. $contentPost .'"';

$datetime = $dateVote;

} elseif ($sessionAct['vote'] == "3") {

$classList = 'actionPost';

$classIcon = 'iconIdiot';

$name = $nameVote;

$content = 'called '. $namePost .' an Idiot for "'. $contentPost .'"';

$datetime = $dateVote;

}

}
?>

<li class="row-activity <?php echo $classList; ?>">

<a href="" class="avatar"><img src="/images/avatars/default_male.jpg" /></a>

<div class="info">

<div class="userName">

<a href=""><?php echo $name; ?></a>

</div>

<span class="msgBody"><?php echo $content; ?></span>

<div class="imgTimeStream">

<span class="img <?php echo $classIcon; ?>"></span>

<span class="source"><?php echo $datetime; ?></span>

</div>

</div>

</li>
<?php

}
} else {

echo '<p>This user has no recent activity.</p>';
}
?>
</ul>


I am by no means an expert programmer...everything I know I have pretty much taught myself over the past year or two, so I can only imagine how ugly this truly is lol. I am learning each and everyday though, and I hope to continue

Also, within each type of activity, there will be slightly different CSS classes on some things.
Example:
if the result row is a post then the css class of the list item would be "post"
if the result row is a vote then the css class of the list item would be "vote"
etc.

Any help would be appreciated!

This topic has been moved to MySQL Help.

http://www.phpfreaks.com/forums/index.php?topic=332033.0

Im doing a search system and Im having some problems. I need to search in two tables (news and pages), I already had sucess doing my search system for just one table, but for two tables its not easy to do. I already use a select statment with two tables using UNION because I want to show number of search results, that is number of returned rows of my first sql statment. But now I need to do a select statment that allows me to acess all fields of my news table and all fields of my pages table. I need to acess in my news table this fields: id, title, content, link, date, nViews I need to acess in my pages table this fields: id, title, content, link Im trying to do this also with UNION, but in this case Im not having any row returning. Do you see what I have wrong in my code?   <?php
//first I get my $search keyword
$search = $url[1];

$pdo = connecting();
//then I want to show number of returned rows for keyword searched
$readALL = $pdo->prepare("SELECT title,content FROM news WHERE title LIKE ? OR content LIKE ?
UNION SELECT title,content FROM pages WHERE title LIKE ? OR content like ?");
$readALL->bindValue(1,"%$search%", PDO::PARAM_STR);
$readALL->bindValue(2,"%$search%", PDO::PARAM_STR);
$readALL->bindValue(3,"%$search%", PDO::PARAM_STR);
$readALL->bindValue(4,"%$search%", PDO::PARAM_STR);
$readALL->execute();
//I show number of returned rows
echo '<p>Your search keyword returned <strong>'.$readALL->rowCount().'</strong> results!</p>';
//If dont return any rows I show a error message
if($readALL->rowCount() <=0){
echo 'Sorry but we didnt found any result for your keyword search.';
}
else{
//If return rows I want to show, if it is a page result I want to show title and link that I have in my page table
//if it is a news result I want to show title and link that I have in my news table and also date of news
echo '<ul class="searchlist">';
$readALL2 = $pdo->prepare("SELECT * FROM news WHERE status = ? AND title LIKE ? OR content LIKE ? LIMIT 0,4
UNION SELECT * FROM pages where title LIKE ? OR content LIKE ? LIMIT 0,4");
$readALL2->bindValue(1, '1');
$readALL2->bindValue(2, "%$search%", PDO::PARAM_STR);
$readALL2->bindValue(3, "%$search%", PDO::PARAM_STR);
$readALL2->bindValue(4, "%$search%", PDO::PARAM_STR);
$readALL2->execute();

while ($result = $readALL2->fetch(PDO::FETCH_ASSOC)){
echo '<li>';
echo '<img src="'.BASE.'/uploads/news/'.$result['thumb'].'"/>';
echo '<a href="'.BASE.'/news/'.$result['id_news'].'">'.$result['title'].'</a>';
//if it is a news result I also want to show date on my list
//echo '<span id="date">'.$result['date'].'</span>';
echo '</li>';
}
echo ' </ul>';
//but how can I do my select statement to have access to my news table fields and my page table fields??
}
?>

i have two tables Loan Application table and Loans table. There is status option and date loan was applied section. After status has been chosen as Pending from the dropdown field and date chosen and loan application saved, it goes under the loans section.

Now, admin has to go and approve the loan and pick the date the loan was approved under the loans section but date can be chosen for the approved date but the status cant be changed to approve because it shows pending. Please what can i do so when i select the date for approval , the status should change to approved automatically

I have 2 queries that I want to join together to make one row  

I am trying to simplify my coding work for my website and wanting to make it operate more professionally (as part of my training to better my programming skills, which I know will take a lot of time to do). One of the things I`d like to improve on it is to create tables and fields in my mysql database, but not have to go into phpmyadmin to do it. I know open source programs like Drupal can do this easily so that when you add modules, the user does not have to access the database to set it up. But looking at the codes, I`m unable to figure out how it works.

How will I be able to program my code to do something like, I`ll have a form where I enter in what I want the new table to be called, what fields to add in and their specifications, where I could do this by just going into my website as the admin?