can some one tell me a little about LEFT JOIN ?

[Joe Pimms]

can any one tell me a little about these in a data base query vb uses please ?

INNER JOIN ?

LEFT JOIN ?

TABLE_PREFIX ?

and if there are any others please do tell

[sabret00the]

table prefix was just done in another thread

INNER JOIN's and LEFT JOIN's are a method used to combine two or more tables.

[Joe Pimms]

i have made a database table but i would like a little help with a query
this is my table can u use this:

PHP Code:

CREATE TABLE `serial` (
  `serialid` int(10) NOT NULL auto_increment,
  `serialname` varchar(255) NOT NULL default '0',
  `userid` int(10) unsigned NOT NULL default '0',
  `dateline` int(10) unsigned NOT NULL default '0',
  `postid` int(10) unsigned NOT NULL default '0',
  `serialnumber` text NOT NULL,
  PRIMARY KEY  (`serialid`),
  KEY `userid` (`userid`),
  KEY `postid` (`postid`)
) TYPE=MyISAM; 


here is what i have here as i did it my self would this work ?

PHP Code:

$query = "SELECT serial.serialid, serial.postid, serial.dateline, serial.userid, IF(user.userid<>0, user.username) AS username
        FROM " . TABLE_PREFIX . "serial AS serial
        LEFT JOIN " . TABLE_PREFIX . "user AS user ON (serial.userid=user.userid)
        order by serial.dateline desc limit $limitlower, $perpage"; 


[miz]

you know , best way to know if something working
is to test it
so test your code
and if its not working
post here the error so we can help you out....

[cinq]

Originally Posted by Joe Pimms

PHP Code:

IF(user.userid<>0, user.username) AS username 


What does this portion mean ?
The part in the IF case.

[Paul M]

Wow, that's a rare use of IF.

Basically it means that the SELECT will only pull the username from the db if the userid is not equal to zero, otherwise it will return NULL (the missing third parameter, which defaults to NULL).

Can you say 'NULL AS username' in a SELECT statement and it assign the value of NULL to username ? - I'm not sure - it might generate an error. I'm certainly not sure why you would want to do this, it seems kinda pointless.

[Joe Pimms]

what im trying to do is be able to view all info in the database table called serial and the other items hold different things...

1.. the serialid
2.. the serialname this is the part that will hold the name of the text or post name
3.. the userid this is the part that holds the user name that makes the posts
4.. the dateline this is the date that the post was made
5.. the serialnumber this is the full post that was made all the text in the post and all

what im wanting to do is also make a add forum and a submit with it as well for a new forum and also have an other php script that will display the post contents as well but the only problem im having is the database not to sure on how to make all the right commands and all so please help me with any ideas or any thing else

thanks so much :smoke:

[miz]

PHP Code:


// lets say we got serialid
$serialid = "56";

$bla = $DB_site->query_first("SELECT
serial.* AS serial, user.userid,user.username AS U-name
FROM " . TABLE_PREFIX . "serial
LEFT JOIN " . TABLE_PREFIX . "user ON(user.userid = serial.userid)
WHERE serial.serialid='$serialid'
");

//spit out the values so we can see all

print_r($bla) 


lets say we want to show last 10 serials

PHP Code:




$bla = $DB_site->quer("SELECT
serial.* AS serial, user.userid,user.username AS U-name
FROM " . TABLE_PREFIX . "serial
LEFT JOIN " . TABLE_PREFIX . "user ON(user.userid = serial.userid)
WHERE serial.serialid='$serialid'
GROUP BY serial.serialid
ORDER BY serial.serialid DESC LIMIT 10
");

while ($bla2 = $DB_site->fetch_array($bla))
{

// ill show how to spit only serial id - you will do the rest alone

$showserialidbit .= "<tr><td>$bla2[serialid];</td></tr>";
}

//spit out the values so we can see all

echo "<table>
<tr><td> serialid </td></tr>
$showserialidbit
</table>"; 


[Joe Pimms]

now if i was to make a forum to add items how would i do that do i use for the database INSTERT or UPDATE ?

also what tables would i need to use in order to enter the info in? cause im adding in a name of the item and then adding the user who posted the item and also the items contents as well ?

can u be able to give me an idea on how to do this plz ?

thanks

[Xenon]

you cannot use JOIN in insert/update queries, just in select queries