PHP - Need Help Joining Two Tables Mysql

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

Hi, I have a table for the users detail in the database which stores their avatar and stuff. There is another called part, this is where the users unique ID from the users table is posted, so I can join these both tables and retrieve the data accordingly. The user table has a few rows for avatars for example avatar_1 , avatar_2 and avatar_3 these rows have paths to different avatars. The problem I am facing is the user table is looking really ugly  thanks to these extra avatar rows. What I want to achieve is create a different table for avatars to store these 3 rows but I was wondering how will I join 3 of these tables the user table, the part table and finally the avatar table or should I do something else?

Thanks any help is appreciated!

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=308889.0

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

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.
   

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

This portion is kind of stumping me. Basically, I have a two tables in this DB: users and users_access_level (Separated for DB normalization)

users: id / username / password / realname / access_level
users_access_level: access_level / access_name

What I'm trying to do, is echo the data onto an HTML table that displays users.username in one table data and then uses the users.access_level to find users_access_level.access_name and echo into the following table data, I would prefer not to use multiple queries if possible or nested queries.

Example row for users: 1234 / tmac / password / tmac / 99
Example row for users_access_level: 99 / Admin

Using the examples above, I would want the output to appear as such:

Username: Access Name: Tmac Admin
I am not 100% sure where to start with this, but I pick up quickly, I just need a nudge in the right direction.

The code I attempted to create just shows my lack of knowledge of joining tables, but I'll post it if you want to see that I did at least make an effort to code this myself.

Thanks for reading!

just trying to see if i have this right in my head here..

if i have a page that has a topic on it and many replies,  the tables would look like this..


would this be the right code to pull out the topic AND all responses for say.
www.mysite.com/view?id=1

$topic = 'SELECT * from topics where id = $id'

$replies = 'SELECT * from replies where replies.topic_id = $id'

I have two tables in which different information of members are stored...

table 1 stores the users main info, name, city, category, location

table 2 stores extra info such as sub categories of items they have.

Now i want to be able to search both tables for one value...say i type in red..i want to be able to search both tables at the same time to find the results and display them

So far i'm stuck in figuring out how to do this, which is why i'm asking for help now.

Hope someone can give me some guidance

I have looked all over the net on how to fix this... I am creating a baseball statistical website and I have 2 tables with identical columns (2012hitting and 2013hitting). I am trying to create a page where I can 1) group each year's statistics (which I have been able to do) and 2) have a row named "Career Totals". Each table has about 15 columns, various stats. How can I add the data from my 2012hitting table to my 2013hitting table into a Career Totals row for each player?

This is what I'm trying now:

Code: [Select]
/* CONNECTION VARIABLES */

$id = $_GET['id']; // get var from URL


/* Get data. */
$sql = "SELECT * (sum(2012hitting.hr) +sum(2013hitting.hr)) as totalhr FROM 2012hitting, 2013hitting WHERE id='$id'";
$result = mysql_query($sql);

?>


<?php
$alternate = "2";
while ($row = mysql_fetch_array($result)) {
$field1 = $row["season"];
$field2 = $row["team"];
$field3 = $row["games"];
$field4 = $row["ave"];
$field5 = $row["slg"];
$field6 = $row["r"];
$field7 = $row["h"];
$field8 = $row["rbi"];
$field9 = $row["bb"];
$field10 = $row["k"];
$field11 = $row["hr"];
$field12 = $row["dbl"];
$field13 = $row["tpl"];
$field14 = $row["sb"];
$field15 = $row["obp"];
$field16 = $row["ops"];
if ($alternate == "1") {
$color = "#ffffff";
$alternate = "2";
}
else {
$color = "#E4E4E4";
$alternate = "1";
}
echo "<tr bgcolor=$color><td align='center'>$field1</td><td align='center'>$field2</td><td align='center'>$field3</td><td align='center'>$field4</td><td align='center'>$field5</td><td align='center'>$field6</td><td align='center'>$field7</td><td align='center'>$field8</td><td align='center'>$field9</td><td align='center'>$field10</td><td align='center'>$field11</td><td align='center'>$field12</td><td align='center'>$field13</td><td align='center'>$field14</td><td align='center'>$field15</td><td align='center'>$field16</td></tr>";
}
echo "</table>";
?>
Thank you in advance!

Okay so I have 2 tables in my database. One called user and one called messages. A user logs in to the message board and leaves a message (eg nice website). They write in the author name and the message then after the message is posted it says

"Nice website"
Posted by (author) on (date).

All is good so far. It works. However if you look at my code you will see I have a session started. This session is storing the username of the logged in user. From the column username in the users table. (This table has has an id for each user). Ive played around with the code trying to make it so the user doesnt have to fill in the author box. I want rid of that box So the logged in user just leaves a message then it says "posted by (username) on (date).

Im missing something from my code.
Can anyone tell me what? Please?

<?php
session_start();
mysql_connect("*************", "*****************", "***************");
mysql_select_db("***********************");
$time = time();



//this checks to see if the $_SESSION variable has been not set 
//or if the $_SESSION variable has been not set to true
//and if one or the other is not set then the user gets
//sent to the login page
if (!isset($_SESSION['username'])) {
    header('Location: http://***************.com/login.php');
}

$query = "INSERT INTO messages VALUES( NULL, '". mysql_real_escape_string($_POST['message']) ."', '". mysql_real_escape_string($_POST['username']) ."', '$time' )";if( $result = mysql_query($query) ) {
        if(mysql_affected_rows() > 0 ) {
                echo "Message Posted.<br><a href='messageboard.php'>Return</a>";
        } else {
                echo 'There was an error posting your message. Please try again later.';
        }
} else {
        echo "There was a database error.";
        // comment out next line for live site.
        echo "<br>Query string: $query<br>Returned error: " . mysql_error() . '<br>';
}


;