/    Sign up×
Community /Pin to ProfileBookmark

PHP/MYSQL Query Between Muliple Tables and Filter Results

Copy linkTweet thisAlerts:
Dec 18.2010

Here is my setup. I have a Drop-Down list of clients that displays a table of their information below it. This displays Name, Address and a lot of other stuff. This works just great.

I just added an attachments section last night to the client with a seperate table in MYSQL. So, I have two tables in MYSQL, Client Information and Attachments. Each table as an ID to join one another, but my problem is that I just don’t know the correct query command to display only the attachments for that individual client.

Below isn’t obviously all the code and I know isn’t close to right. I assume I just need to figure out how to associate “clientid_files” with the selected item id from the drop-down box. The belows code displays all of the matching items that are matching amongst the two tables.

Again, I want to display only the attachments associated with selected client from the drop-down list.

Here is what I have:

[CODE]<?

$query = “SELECT clientid_files, id, name, size, created “.
“FROM file, clients “.
“WHERE clientid_files = id”;

$result = mysql_query($query) or die(mysql_error());
?>
<?
while($row = mysql_fetch_array($result)){

echo ”
<tr>
<td>{$row[‘name’]}</td>
<td>{$row[‘size’]}</td>
<td>{$row[‘created’]}</td>
<td><a href=’up/get_file.php?id_files={$row[‘id_files’]}’>Download</a></td>
<td><a href=’up/deletefile.php?id_files={$row[‘id_files’]}’>Delete</a></td>
</tr>”;

}

echo ‘</table>’;

?>

</center>
</form>
[/CODE]

to post a comment
PHP

5 Comments(s) ↴

Copy linkTweet thisAlerts:
@jcavemanDec 19.2010 — You're on the right track. The format of the query is:

[code=php]$SELECT [fields] FROM [table] WHERE [field] = [value][/code]

so (assuming that your table name is 'clientid_files') you could us this:

[code=php]$query = "SELECT id, name, size, created FROM clientid_files WHERE id = '".$id."'";[/code]

This also assumes that you've stored the ID of the client in question in a variable called '$id'.
Copy linkTweet thisAlerts:
@tjberteauthorDec 19.2010 — You're on the right track. The format of the query is:

[code=php]$SELECT [fields] FROM [table] WHERE [field] = [value][/code]

so (assuming that your table name is 'clientid_files') you could us this:

[code=php]$query = "SELECT id, name, size, created FROM clientid_files WHERE id = '".$id."'";[/code]

This also assumes that you've stored the ID of the client in question in a variable called '$id'.[/QUOTE]


I thought we had it!!!!..but something still not working. My selected variable was in fact 'id', but now it only displays uploads that have a blank id in the uploads table. If they have an id associated with them in uploads they will NOT show up. Almost like either $id or id doesn't have anything associated with it.
Copy linkTweet thisAlerts:
@jcavemanDec 19.2010 — I apologize- I misunderstood your situation. Looks what what you need is a SQL Join. I'll work on that and get back to you.
Copy linkTweet thisAlerts:
@jcavemanDec 19.2010 — Here is an example of a SQL join that [I]may[/I] work for you. Where yours might differ from this would be in which fields you want returned, and your overall database schema.
[code=php]
$query = "SELECT c.clientid_files, c.id, f.name, f.size FROM clients as c JOIN file as f on c.clientid_files = f.clientid_files WHERE c.id = '".$id."'"; [/code]
Let me know if this helps.
Copy linkTweet thisAlerts:
@tjberteauthorDec 24.2010 — Actually your first one worked for me caveman, with one little modification.

[CODE]$query = "SELECT id_files, clientid_files, name, size, created FROM file WHERE clientid_files = '".$row['id']."'";[/CODE]
×

Success!

Help @tjberte spread the word by sharing this article on Twitter...

Tweet This
Sign in
Forgot password?
Sign in with TwitchSign in with GithubCreate Account
about: ({
version: 0.1.9 — BETA 5.18,
whats_new: community page,
up_next: more Davinci•003 tasks,
coming_soon: events calendar,
social: @webDeveloperHQ
});

legal: ({
terms: of use,
privacy: policy
});
changelog: (
version: 0.1.9,
notes: added community page

version: 0.1.8,
notes: added Davinci•003

version: 0.1.7,
notes: upvote answers to bounties

version: 0.1.6,
notes: article editor refresh
)...
recent_tips: (
tipper: @AriseFacilitySolutions09,
tipped: article
amount: 1000 SATS,

tipper: @Yussuf4331,
tipped: article
amount: 1000 SATS,

tipper: @darkwebsites540,
tipped: article
amount: 10 SATS,
)...