...

View Full Version : PHP and fetching from a relational database



blueuniverse
07-21-2008, 03:51 PM
I'm still a beginner at PHP and having worked all day so far on other things my brain has kind of hit capacity, so apologies if this is easy.

Anyway, my table is set up so I have

library
filename
artist name
track name
catid

then I have a category table
id
category

My catid input is set to use the different fields that are in category input.


But how do I display the category name when I specify the library catid?

My code currently is as follows


// Get all the data from the "library" table
$result = mysql_query("SELECT * FROM library")
or die(mysql_error());

// see if any rows were returned
if (mysql_num_rows($result) > 0) {
// yes
// print them one after another
echo "<table>";
while($row = mysql_fetch_row($result)) {
echo "<tr>";
echo "<td>".$row[1]."</td>";
echo "<td>".$row[2]."</td>";
echo "<td>".$row[3]."</td>";
echo "<td>".$row[4]."</td>";
echo "</tr>";
}
echo "</table>";
}
else {
// no
// print status message
echo "No rows found!";
}

row4 is the catid in the library database. How do I get this bit to show the category name?

Thanks in advance,
Ed

oesxyl
07-21-2008, 03:56 PM
start counting from 0. $row[0] is library.

regards

blueuniverse
07-21-2008, 04:12 PM
Apologies, explained my table structure a bit quickly there.

library (table name)
id
filename
artist name
track name
catid


The catid specifies a non unique number that I then need to link to the category table, match the id of that and then return the category name.

oesxyl
07-21-2008, 04:30 PM
Apologies, explained my table structure a bit quickly there.

library (table name)
id
filename
artist name
track name
catid


The catid specifies a non unique number that I then need to link to the category table, match the id of that and then return the category name.



$query = "select l.id, l.filename, l.artist_name, l.track_name, l.catid, c.category from library as l, category as c where l.catid = c.id";
$result = mysql_query($query);
if($result){
while($row = mysql_fetch_assoc($result)){
foreach($row as $field => $value){
print '<td>'.$value.'</td>';
}
}
}


PS: make the code to print table and tr and if you want you can use field name from $field.

regards

blueuniverse
07-22-2008, 12:18 PM
Apologies but I can't get it to format in a similar manner nor can I get it to selectively display fields.

My bad if this is again easy but I'd only really just got my head around the existing code, I've played about for a while using similar methods but I can't get it to print out in a similar manner.

Thanks in advance for your help.

oesxyl
07-22-2008, 12:32 PM
Apologies but I can't get it to format in a similar manner nor can I get it to selectively display fields.

My bad if this is again easy but I'd only really just got my head around the existing code, I've played about for a while using similar methods but I can't get it to print out in a similar manner.

Thanks in advance for your help.
not clear for me what is the problem but:
- you must use the names of the fields from your database. names like 'artist name' or 'track name' must be quoted in mysql or as I do replace the spaces with '_'.
- $row is a hash array, so you can see the keys and the values using print_r or echoing the $field and $value variable from the foreach. you can also address some field for example like:


echo $row['catid'];


I hope I don't missunderstand the problem but in case I do, post what I have unclear and I try to help.

put this in foreach loop:


echo $field.": ".$value."<br>";

and see ehat happend

regards

blueuniverse
07-22-2008, 02:48 PM
This is displaying as follows


0: id
1: trackname
2: artist
3: file_name
4: catid
5: category

Whereas before it displayed as follows


Track Name Artist Filename Catid

As far as formatting goes I want it to display exactly as before in a row just with the catid replaced with the category name.

i.e. I need a way to place the data in a row and to remove fields, I don't for example want the id or the catid to display.

Thanks,
Ed

oesxyl
07-22-2008, 02:59 PM
This is displaying as follows
Whereas before it displayed as follows

As far as formatting goes I want it to display exactly as before in a row just with the catid replaced with the category name.

i.e. I need a way to place the data in a row and to remove fields, I don't for example want the id or the catid to display.

Thanks,
Ed
please post the code you have now.
I don't know what you use to show the data this way, so we start from your code and modify until work how you want.

regards

PeaTearGriffin
07-22-2008, 03:29 PM
I think what he wants is like this. I think this should work:


while($row = mysql_fetch_assoc($result)) {
echo "<tr>";
echo "<td>".$row['trackname']."</td>";
echo "<td>".$row['artist']."</td>";
echo "<td>".$row['file_name']."</td>";
echo "<td>".$row['category']."</td>";
echo "</tr>";
}


Or instead of selecting everything in your SQL, you can just select those 4 columns and then print out those 4 results with foreach.

oesxyl
07-22-2008, 03:38 PM
I think what he wants is like this. I think this should work:


while($row = mysql_fetch_assoc($result)) {
echo "<tr>";
echo "<td>".$row['trackname']."</td>";
echo "<td>".$row['artist']."</td>";
echo "<td>".$row['file_name']."</td>";
echo "<td>".$row['category']."</td>";
echo "</tr>";
}


Or instead of selecting everything in your SQL, you can just select those 4 columns and then print out those 4 results with foreach.
that's why I ask for actual code. 'category' is in other table then 'file_name' therefor must use a where to join the tables and probably op don't use mysql_fetch_assoc, I susspect it use mysql_fetch_row or mysql_fetch_array. Are too many assumption, :)

regards

PeaTearGriffin
07-22-2008, 05:08 PM
that's why I ask for actual code. 'category' is in other table then 'file_name' therefor must use a where to join the tables and probably op don't use mysql_fetch_assoc, I susspect it use mysql_fetch_row or mysql_fetch_array. Are too many assumption, :)

regards

Oh didn't see that as he stated 6 rows were displayed... Maybe change the select statement to this?


SELECT trackname, artist, file_name, category
FROM library l INNER JOIN category c
ON l.catid = c.id

oesxyl
07-22-2008, 05:15 PM
Oh didn't see that as he stated 6 rows were displayed... Maybe change the select statement to this?


SELECT trackname, artist, file_name, category
FROM library l INNER JOIN category c
ON l.catid = c.id

the result from #7 (http://www.codingforums.com/showpost.php?p=714523&postcount=7) use the echo posted in #6 (http://www.codingforums.com/showpost.php?p=714523&postcount=6) and the query from
#4 (http://www.codingforums.com/showpost.php?p=714523&postcount=4), :)

your query could get rid of l. and c. from first part of the select, I notice only now, :)
is a good idea but I never like join( without reason), :)

regards

winnard2008
07-22-2008, 08:18 PM
Hi


I too am in a similar situation to blueuniverse, I have a database with two tables one with a list of categories that link to a select dropdown in a form, this table called categories and the other table has information in like name, etc etc called portfolio.

What blue universe is trying to achieve from what I can gather is listing everything in library table by a category in the category table. and then displaying the results.


Now at work I have a piece of code that if you have the same issue as me, then it will work how you want it to.


The thing i have found with PHP forums is not many people read the initial problem and or the poster does go into enough depth.

Therefore it takes you twice as long to sort the problem out.


I have been learning PHP for two weeks and have not really had any code from forums that has actually helped me.


Once become fluent in php I will become Mother Teresa of the PHP world, and will actually help somebody solve a problem.


Ifind that by having the code in front of you complete, then you learn quicker than tying to solve it yourself.



Let me know if you dont sort this problem out and i will post the code tomorrow.


Danny

blueuniverse
07-23-2008, 05:27 PM
I think what he wants is like this. I think this should work:


while($row = mysql_fetch_assoc($result)) {
echo "<tr>";
echo "<td>".$row['trackname']."</td>";
echo "<td>".$row['artist']."</td>";
echo "<td>".$row['file_name']."</td>";
echo "<td>".$row['category']."</td>";
echo "</tr>";
}


Or instead of selecting everything in your SQL, you can just select those 4 columns and then print out those 4 results with foreach.

This did the trick, thanks for all your help - made huge progress because of it.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum