PHP - 3 Table Join Problem

I have two mysql tables, one is called "posts", the other is called "removed_posts", I have joined them together and can get some of the information from the tables, but I want to be able to get all of the information.

This is my query, and the variables I need the information to go to:

Code: [Select]
$quer = mysql_query("SELECT posts.id, removed_posts.post_id FROM posts, removed_posts WHERE posts.id=removed_posts.post_id AND removed_posts.user_id='".$session."'");
while($get_post = mysql_fetch_array($quer))
{
$session = $_COOKIE['id'];
$to_id = $get_post['to_id'];
$from_id = $get_post['from_id'];
$post = linkify(nl2br(htmlentities($get_post['post'])));
$date = time_stamp($get_post['date']);
$id = $get_post['id'];
What that's doing is selecting the id from 'posts' table, and selecting the post_id from the 'removed_posts' table (The query checks to make sure posts.id is equal to removed_posts.post_id), it does that just fine, since I defined what information I wanted it to get in the query, however, now I need to get the other information which will come from the 'posts' table.

$get_post['to_id'],
$get_post['from_id'],
$get_post['post'], and
$get_post['date']

I hope that's not toooooo confusing, any help would be appreciated

I'm trying to conditionally add a table to a select query using the join, but I always get an "no such table"  error.  I'm not at all sure if the syntax is correct, so I decided to ask.    I'm using the sqlite C API interface and my tables and query were constructed using sprintf, so please ignore any %d, %s that may appear

CREATE TABLE IF NOT EXISTS tbl_master
(
     id INTEGER PRIMARY KEY AUTOINCREMENT,
     volume TEXT(16) UNIQUE NOT NULL DEFAULT '',
     note   TEXT(%d) NOT NULL DEFAULT '',
     items INTEGER NOT NULL DEFAULT 0
);
 
CREATE TABLE IF NOT EXISTS tbl_file
(
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      volume_key TEXT(16) NOT NULL DEFAULT '',
      name TEXT(%d),
      size TEXT(20),
      type TEXT(4),
      path TEXT(%d),
      file_id INTEGER
);
 
CREATE TABLE IF NOT EXISTS tbl_hash
(
     id INTEGER PRIMARY KEY AUTOINCREMENT,
     hash TEXT(33) NOT NULL DEFAULT '',
     volume_key TEXT(16) NOT NULL DEFAULT '',
     file_key INTEGER NOT NULL DEFAULT 0
);
 
CREATE TABLE IF NOT EXISTS tbl_media
(
       id INTEGER PRIMARY KEY AUTOINCREMENT,
       runtime TEXT(10) NOT NULL DEFAULT '',
       frame TEXT(12) NOT NULL DEFAULT '',
       type_key TEXT(4) NOT NULL DEFAULT '',
       hash_key TEXT(33) NOT NULL DEFAULT '',
       volume_key TEXT(16) NOT NULL DEFAULT '',
       file_key INTEGER NOT NULL DEFAULT 0
);

I milled over a few JOIN tutorials, but I'm still unclear on the exact usage of JOIN; I'm trying to add the media table if the file type is a vid, snd, or pix, but I need file information regardless.  I've tried various flavors of the following query, but each time I get the table doesn't exist error.  Selects, deletes, updates, inserts work on all tables, so I'm guessing my syntax is wrong with the JOIN.

SELECT tbl_file.*, tbl_hash.hash
  FROM tbl_file AS f, tbl_hash AS h
  LEFT OUTER JOIN tbl_media AS m
  ON  ((f.type='VID' OR f.type='SND' OR f.type='PIX')
  AND m.file_key=f.file_id)
  WHERE ((f.volume_key=tbl_master.volume
  AND (h.volume_key=tbl_master.volume AND h.file_key=f.file_id)))
  ORDER BY f.path ASC;

Any ideas on how to pull off what I'm trying to do would be greatly appreciated.  Thank you for your time.      

hi,

I am somewhat of a newbie and think this is a simple fix, but I have been having problems and can't seem to figure it out. I am trying to JOIN two tables, but am not getting the results I would like. I am getting the correct question number displayed whenever I try different instances, but it always defaults to the "DTCAP" question (q_code).   I have included the snippet of code and how the table structure looks. Any help would be greatly appreciated!   -------------------------------------------------------------------------------------   //Testing to ensure that the $_SESSION['code'] is DTCAP, CNROW, or MONFW after user chooses option echo $_SESSION['code'];   $query_question=mysql_query("SELECT * FROM adventure_questions q
            INNER JOIN users_adventures u ON q.q_number = u.ua_question
            WHERE u.ua_username = '$_SESSION[username]' AND u.ua_code='$_SESSION[code=auto:0]'");   $question_info=mysql_fetch_array($query_question);   echo $question_info['q_number'].". ".$question_info['question'];   -------------------------------------------------------------------------------------   adventure_questions q_id     q_code       q_number     question 1          DTCAP              1         DTCAP question 1 2          DTCAP              2         DTCAP question 2 26       CNROW              1        CNROW question 1 27       CNROW              2        CNROW question 2 51       MONFW              1        MONFW question 1 52       MONFW              2        MONFW question 2   users_adventures ua_id    ua_username       ua_code      ua_question    ua_score 1                kjb31ca             DTCAP              1                   0 2                kjb31ca            CNROW              2                 10 3                kjb31ca            MONFW              1                  0
Edited by KjB31ca, 20 May 2014 - 06:39 AM.

Hello guys. Thanks a lot for any help with this. My problem is that I'm trying to use MAX to get the last item in the portafolio table. This is my query:

$result = $db->prepare( "SELECT * FROM portafolio INNER JOIN imagenes ON imagenes.work = portafolio.idp WHERE work = ? ORDER BY iid DESC);
$result->execute(array($_GET['work']));

The main idea is not use $_GET['work'], because i want to display the last work (with their respective images) in my portafolio in the main page, so im not passing any variable by url.  

This topic has been moved to MySQL Help.

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

I have the following PHP code...
$query1 = $this->db->query("SELECT `categories`.`cat_id`, `categories`.`cat_name`, `forums`.`forum_name`
                                        FROM `".$this->dbname."`.`categories`
                                        LEFT JOIN `".$this->dbname."`.`forums`
                                        USING (`cat_id`)
                                        ORDER BY `categories`.`cat_order` ASC");

              $i = 0;
                while($row = $this->db->fetch_assoc($query1))
                {
                    $content .= ($i % 2 == 0 ? '<tr class="odd">' : '<tr>');
                    $content .= '<td><p><a href="index.php?cg=cgs&page=forum&do=forums:'.$row['cat_id'].'">'.$row['cat_name'].'</a></p>';
                    $content .= ' - '.$row['forum_name'].'</td>';
                    $content .= '</tr>';
                    $i++;
                }

I'm getting duplicate rows of the first table (categories) being outputted. So I'm currently getting...
Quote

Crikey Games
- Suggestions and Ideas
Crikey Games
- General Discussion
Crikey Games
- Announcements
Realm Battles
- Bugs, Glitches and Exploits
Realm Battles
- Recruiting
Realm Battles
- General Discussion
Realm Battles
- Suggestions and Ideas
Realm Battles
- Battle Grounds
Realm Battles
- Announcements


When I want to be getting...
Quote

Crikey Games
- Suggestions and Ideas
- General Discussion
- Announcements

Realm Battles
- Bugs, Glitches and Exploits
- Recruiting
- General Discussion
- Suggestions and Ideas
- Battle Grounds
- Announcements


Code: [Select]
--
-- Table structure for table `categories`
--

CREATE TABLE IF NOT EXISTS `categories` (
  `cat_id` int(10) NOT NULL AUTO_INCREMENT,
  `cat_name` varchar(50) NOT NULL,
  `cat_order` int(5) NOT NULL DEFAULT '0',
  PRIMARY KEY (`cat_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

--
-- Dumping data for table `categories`
--

INSERT INTO `categories` (`cat_id`, `cat_name`, `cat_order`) VALUES
(1, 'Crikey Games', 1),
(2, 'Realm Battles', 2);

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

--
-- Table structure for table `forums`
--

CREATE TABLE IF NOT EXISTS `forums` (
  `forum_id` int(10) NOT NULL AUTO_INCREMENT,
  `cat_id` int(10) NOT NULL,
  `forum_name` varchar(50) NOT NULL,
  `forum_description` varchar(225) NOT NULL,
  `forum_order` int(5) NOT NULL DEFAULT '0',
  `forum_locked` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`forum_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=10 ;

--
-- Dumping data for table `forums`
--

INSERT INTO `forums` (`forum_id`, `cat_id`, `forum_name`, `forum_description`, `forum_order`, `forum_locked`) VALUES
(1, 1, 'General Discussion', 'talk about anything you like', 2, 0),
(2, 1, 'Announcements', '', 1, 0),
(3, 1, 'Suggestions and Ideas', 'if you have a suggestion or idea, share it here', 3, 0),
(4, 2, 'Announcements', '', 4, 0),
(5, 2, 'General Discussion', 'talk about anything Realm Battles related here', 5, 0),
(6, 2, 'Battle Grounds', '', 6, 0),
(7, 2, 'Recruiting', '', 7, 0),
(8, 2, 'Suggestions and Ideas', 'if you have a suggestion or idea for Realm Battles, share it here', 8, 0),
(9, 2, 'Bugs, Glitches and Exploits', 'if you have discovered something that needs to be fixed, share it here', 9, 0);

Not sure whether I'm doing something wrong with the PHP side of things, or the MySQL query.

Also I can't figure out how to ORDER BY `forums`.`forum_order` ASC , when trying to add it to this query.

Thanks in advance,
Ace

This topic has been moved to MySQL Help.

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

This topic has been moved to MySQL Help.

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

hirealimo.com.au/code1.php

this works as i want it:
Quote

SELECT * FROM price INNER JOIN vehicle USING (vehicleID) WHERE vehicle.passengers >= 1 AND price.townID = 1 AND price.eventID = 1



but apparelty selecting * is not a good thing????

but if I do this:
Quote

SELECT priceID, price FROM price INNER JOIN vehicle....etc


it works but i lose the info from the vehicle table.

but how do i make this work:
Quote

SELECT priceID, price, type, description, passengers FROM price INNER JOIN vehicle....etc


so that i am specifiying which colums from which tables to query??

thanks

hi...

i have a table ...

i add and remove data in the table...when i add new record , information add to center of the table ! whats problem?   i want add data in first of table.   please guide me.thanks

 

 

Hi

I want to know I can select which ID I get from an inner join?

Not sure If I should post here or in the MYSQL forum, but as it's using OOP I posted here.


Code: [Select]
$sql = "SELECT * FROM area_county INNER JOIN area_country ";
$sql .= "ON area_county.country_id = area_country.id ";
$sql .= "ORDER BY area_country.country, area_county.county ASC ";
$sql .= "LIMIT {$per_page} ";
$sql .= "OFFSET {$pagination->offset()}";
$list = Area_county::find_by_sql($sql);

foreach($list as $lists){
$ID = $lists->id;
$country_id = $lists->country_id;
$county = ucwords($lists->county);
        $country = ucwords($lists->country);

echo $ID;
}


I want it to show the ID of the county, NOT the id of the country.
both tables in the database have the ID column called id.

I know this can be done, but not to sure how.


Thanks

Hello everyone,   I am hopping someone can help me sort out a query.   I have two tables, one table is a players table, and a second table is a player_vitals data that stores heights and weight changes.  I want to list all the players showing the latest height and weight change.   this query will not fetch the latest height and weight   SELECT a.player_id, a.player_first_name,a.player_last_name, b.player_height, b.player_weight

I'm an amateur PHP/mySQL person, trying to learn some more things, and I've hit a snag trying to do something I haven't done before.

Okay, here's what I'm trying to do. I have 3 tables:

Items (which includes `id` as the primary key) and a TON of information about each item.
Shop (which includes `id` as the primary key) and shop name and location.
Sale which has shop_id and item_id.

So, what I'm trying to do is have a form where someone can input in an item name and a shop name and have that item then added to the shop. This is where the Sale table comes into play too, as that's going to house each of the items and what shop it's in.

I currently have the following queries:


       $query5 = $db->execute("SELECT id FROM items LEFT JOIN sale ON items.id=sale.item_id");
       $query6 = $db->execute("SELECT id FROM shop LEFT JOIN sale ON shop.id=sale.shop_id");


I have the following form:


        if ($name == "") { $errors++; $errorlist .= "Name is required.<br />"; }
        if ($location == "" ) { $errors++; $errorlist .= "Location is required.<br />"; }
if (!isitem($itemname)) die("That item does not exist."); }

        if ($errors == 0) { 
            $query = doquery("INSERT INTO `sale` SET `item_id`=`$item`");
            admindisplay("New Item Added.","Add Items");

$page = <<<END

<b><u>Add Item</u></b><br /><br />
<form action="create_shop.php?do=additem" method="post">
<table width="90%">
<tr><td width="20%">Name:</td><td><input type="text" name="itemname" size="30" maxlength="255" value="" />**Be sure the item is already in the database and matches it IDENTICALLY.</td></tr>
<tr><td width="20%">Location:</td><td><input type="text" name="location" size="30" maxlength="55" value="" /><br /><span class="small">Where is the item obtained (unique shop name, please!)</span></td></tr>
</table>
<input type="submit" name="submit" value="Submit" /> <input type="reset" name="reset" value="Reset" />
</form>


What next steps do I need to take? Am I even on the right path?

Thanks for any and all help anyone provides!! I really appreciate it!

Not sure if it is because it is too early in the morning but I am having a problem.

Code: [Select]
$mpid = #

$query = "SELECT table1.title, table2.* ".
 "FROM table1, table2 ".
"WHERE table1.mpid = table2.mpid";

How do I only show the info from the 2 tables where mpid=#