MySQL Question (Table -> Table)

[Jerrik]

Is it possible to have a table in a table? Sort of like a record within a record? [turing]

I had a few things I wanted to do with a MySQL database (I've only just started learning PHP/MySQL/Javascript) regarding a table in a table. If not I'll just do do a psuedo pointer system but I thought I'd ask.

I also wanted to ask how to draw an image from a blob in a MySQL database? I spent a good three hours trying to draw an image stored in a blob, but nothing I've tried has worked.

A lot of the methods I tried were sending information to the header telling it that I am sending an image, and then echoing the blob (the image) but it kept telling me I wasn't allowed to update the header. Here's the error;

Quote:

Warning: Cannot modify header information - headers already sent by (output started at c:\program files\easyphp1-8\www\jerrik\display_pictures.php:9) in c:\program files\easyphp1-8\www\jerrik\display_pictures.php on line 22

I haven't sent anything to the header except for one line.

PHP code snippet wrote:

$Request=mysql_query ("SELECT content FROM user_Pics");
while ($Row = mysql_fetch_array($Request))
{
header("Content-type: image/jpeg");
echo $Row['content'];
}

I took out the part that includes me getting the type of image and all that jazz. Right now its just getting "content" which is the blob, and trying to echo it. But all it does is out put the binary data because the header doesn't know its an image due to the error.

Any help would be greatly appreciated, thank you.

[rdrake]

A table in a table? I doubt it. You can however relate two tables together with foreign keys.

Let's taken an example:

code:

users     | posts ----------------- username  | title password  | message join_date | user_id

Notice how posts are related to users by the use of the user_id column in the posts table?

Not sure if this is exactly what you want, but this is a very common thing to see in a database.

[Dan]

Jerrik @ 25th June 2007, 7:01 pm wrote:

Is it possible to have a table in a table? Sort of like a record within a record? [turing]

Not in MySQL, at least not in the way you mean. There are some dbms that have mulitdimational tables witch could do somting like it but MySQL is a realtional dbms. What you probly whont is to put the primany key of the record you whont in another table in the record of the table you have, this is noramly called a foreine key. (see rdrake's post).

Quote:

I also wanted to ask how to draw an image from a blob in a MySQL database? I spent a good three hours trying to draw an image stored in a blob, but nothing I've tried has worked.

You can store images in databases but it is generaly a very bad idea. Esptaly for web desing applications. It is almost allways better to upload the image to the web server as a noraml file and store the url to it in the database. This saves alot of processing and loading time becues it is faster to get a string form the database and then tell the users browser to load it. It means less stress on your server and it is almost allways faster for the user.

[Jerrik]

rdrake @ Mon Jun 25, 2007 7:43 pm wrote:

A table in a table? I doubt it. You can however relate two tables together with foreign keys.

Let's taken an example:

code:

users     | posts ----------------- username  | title password  | message join_date | user_id

Notice how posts are related to users by the use of the user_id column in the posts table?

Not sure if this is exactly what you want, but this is a very common thing to see in a database.

That is what I am currently doing, with the "PRIMARY KEY(id)" part of the user table, it just seems like a waste of time (processing, not on my end) to look at another table, cycle through it, and just select that users messages. I guess thats what needs to be done though, thank you.

Dan @ Mon Jun 25, 2007 8:51 pm wrote:

Jerrik @ 25th June 2007, 7:01 pm wrote:

Is it possible to have a table in a table? Sort of like a record within a record? [turing]

Not in MySQL, at least not in the way you mean. There are some dbms that have mulitdimational tables witch could do somting like it but MySQL is a realtional dbms. What you probly whont is to put the primany key of the record you whont in another table in the record of the table you have, this is noramly called a foreine key. (see rdrake's post).

Quote:

I also wanted to ask how to draw an image from a blob in a MySQL database? I spent a good three hours trying to draw an image stored in a blob, but nothing I've tried has worked.

You can store images in databases but it is generaly a very bad idea. Esptaly for web desing applications. It is almost allways better to upload the image to the web server as a noraml file and store the url to it in the database. This saves alot of processing and loading time becues it is faster to get a string form the database and then tell the users browser to load it. It means less stress on your server and it is almost allways faster for the user.

Thats probably what I will end up doing, since a lot of people swear against doing it that way and suggest the URL method.

The problem with the header (all the errors) was the php config had output_buffering set to off, so as far as I know (I'm just guessing since I'm new to this stuff) when I was sending information to the header it was "full" already, and reported that information had already been sent when it hadn't.

Now instead of displaying raw binary data it will only display, "http://127.0.0.1/Jerrik/Display_Pictures.php". It doesn't draw the image, and again I'm at a loss as to why. If anyone has any reason why I'd appreciate it. I probably won't be using this method for displaying images or files, but it bugs me to not have a solution.

Anyway, thanks for all the help!

[octopi]

In the code you posted it looks like your trying to display multiple images at once, you can really only print one image at a time to the browser, basically get rid of the while loop, and make sure that in your database blob you've stored the image file's contents (binary data, or base64'ed)


Also mysql optimizes queries with multiple tables pretty effectively so it isn't really a waste of too many resources.

code:

select p.message from posts as p, users as u where p.user_id=u.user_id and u.username='jeb'

Thats off the top of my head, so there might be some mistakes, but basically that would let you access the messages from a database if you only know the user's username, by using a common key (user_id)