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:

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 ?

$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]

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]

// 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





$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

[Michael Morris]

user.userid<>0, user.username
[/PHP]

If we're talking vbulletin, user.userid is an autoincremented primary key table - it will never be 0 so that conditional will always eval true.

[Joe Pimms]

can some one help with the coding on this for me please i have a template for a forum to add items to a database $stylevar[htmldoctype]
<html dir="$stylevar[textdirection]" lang="$stylevar[languagecode]">
<head>
$headinclude
</head>
<body>
$header
$navbar
<form action="add_serial.php" method="post" target="_self">
<table class="tborder" cellpadding="$stylevar[cellpadding]" cellspacing="$stylevar[cellspacing]" border="0" width="80%" align="center">
<thead>
<tr>
<td class="tcat" colspan="2">$vbphrase[Submit_Your_Serial]</td>
</tr>
</thead> <tr> <td width="97" valign="top">Programm Name:</td>
<td width="493"><input name="add_name" value="$app_name" type="text" id="add_name"></td>
</tr>
<tr>
<td valign="top">Serial:</td>
<td><textarea name="add_serial" cols="40" rows="5" id="add_serial">$add_serial</textarea></td>
</tr>
<tr>
<td valign="top">&nbsp;</td>
<td>
<input type="submit" value="Submit"><input name="go" type="hidden" value="add"></td>
</tr>
</table></form>
$footer
</body>
</html>

i just need help with the phpscript to add data to the database

here is the tables for the database
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',
`serialnumber` text NOT NULL,
PRIMARY KEY (`serialid`),
KEY `userid` (`userid`)
) TYPE=MyISAM;

[Creative Suite]

That's may help you

http://www.w3schools.com/sql/sql_join.asp