PHP - Joining Tables

This topic has been moved to MySQL Help.

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

$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()); 

 

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

This topic has been moved to MySQL Help.

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

I have a relational table call sales with prodcut_id and custmer_id tables. I also have a product and customer tables. products table with product_id as an auto increment and customer with custumer_id I want to join through the sales tables all t he fields of row 1 from tables customer and products and pull it at once.


This is a member login script and I want to display the products by members.

So far I have this query to display the products once the member is login in. $userid is the id of the customer coming from the $userid= $_SESSION['customer_id'];


$mysqlSales="SELECT products.* FROM products JOIN sales ON (products.product_id = procuct_id ) WHERE sales.customer_id = '$userid'";


so far that statement is not working where should I have some type of incoherance with the english statement above expressing what I want the query to do.

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!

Hello,   I want to show checkbox is checked when there is entry of that id in a table in my database.    I have 2 tables page and access_level. I am getting data from page table and displays it in <ul><li> tag with checkbox to select all or only few. After selecting the checkbox, i will store only selected checkbox value in access_level table along with table id. Page link and page name details will be stored in page table.    Now if i want to edit , i should display all the pages which is there in page table and i should also mark checked to those which are already stored in access_level table.   I am using LEFT OUT JOIN, It displays all the pages. But it is not displaying the check mark to those which are already selected.   Here is my code

<?php $s1 = mysql_query("SELECT pages.page_id, pages.code, pages.page, pages.href, access_level.aid, access_level.page_id as pgid, access_level.department, access_level.position, access_level.active FROM pages LEFT OUTER JOIN access_level ON pages.page_id=access_level.page_id WHERE access_level.department=".$department." AND access_level.position=".$position." AND pages.code='sn'") or die(mysql_error());
				while($s2 = mysql_fetch_array($s1)) { ?>
				<tr><td><li><?php echo $s2['page']; ?>  </td><td><input type="checkbox" name="sn[]" value="<?php echo $s2['page_id']; ?>"  
                <?php if($row['pgid'] === $s2['page_id']) echo 'checked="checked"';?> />

here is my pages table     pages.JPG   26.55KB   0 downloads   access_level  access_level.JPG   19.09KB   0 downloads   In access_level table i do not have page ids 8 and 9. But i want to display that also from pages table and for 1 to 7 and 10 i should display check mark.    How i can achieve this? Please Help

This topic has been moved to MySQL Help.

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

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

What I want is basicly a newspaper function.

I have a table like this:

name hone website email name1 hone1 website1 email1
So what i want is creating a mail link that takes the 2 mailadresses from my table. 1 email with 2 reciepts. Just like you seperate them with the ; in you mailclient

mailto:email; email1


Hoping someone can help, I think it is not that hard, I am just a nOOb

Hello guys. I'm pulling my hair out over this one, and some help will def be appreciated. Here are the 2 tables:

table 1: prizes
id prize_name max_winners win_type 1 $1 5 daily 2 $2 2 daily 3 $5 1 weekly
table 2: logs
id prize_id win_time 1 1 1280494274 2 1 1280494300 3 3 1280493899
How can I make it so that I can join the 2 tables, using prize.id=log.prizeid, then choose a prize that has not reached maximum winners for the period (win_type).

Query which I have been working on which isn't working properly:

Code: [Select]
select prize.id,prize.prize_name,ifnull(county,0),tp.prize_id,prize.max_winners,tp.win_time,prize.win_type from prizes prize
left join (
select prize_id,win_time,count(*) as county from logs
) tp
on prize.id=tp.prize_id where prize.max_winners>'0' and ((prize.win_type='once' and prize.max_winners>county) or (prize.win_type='hourly' and tp.win_time>'$_last_hr')) order by rand() limit 1
There is a few different "win_types"...
-once (which only gets awarded once)
-hourly
-daily
-weekly

I can get it to select a prize, but as soon as I add the "county" it stops working. I've tried so many methods to get this work but now it's time to throw in the towel and get some help please.

Thanks in advance. If you require more info please let me know.

how can i join the two queries so that they function as one. for example, if $keywords or $username is empty then the result will be null.

$query1 = "SELECT * FROM forums WHERE MATCH (topics) AGAINST ('$keywords' IN BOOLEAN MODE)";

$query2 = "SELECT * FROM users WHERE username='$username";

hello,

here is what i have, obviosly it doesnt work.

Code: [Select]
$year=$_POST['y'];
$yr=substr($year,-2);
$mth=$_POST['m'];
$init=$_POST['initial'];
$jobnumber=$yr$mth2343$init;

echo "$jobnumber";

P.S.  thanks to everybody for their help lately!

This topic has been moved to MySQL Help.

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

I got two statments:

"SELECT id FROM approvednotes WHERE authoer = '$username'";
"SELECT nid FROM usersdownloaded WHERE username - '$author'";

and then once I get results from both of those I want to search approved notes again using only the matches from the statements above and what I want to select this time is "SELECT * FROM approvednotes WHERE md5 ='$md5'"; But I dont actually want to search all the listings just the ones that returned true in the first two statements, is there a way to join these three statements into one? I hope I made myself clear if not let me know and ill try to reexplain.