Page 1 of 1

Iterating through database query, and inserting into tables

Posted: Thu Feb 08, 2007 2:23 pm
by dwessell
Hey all..

I have a query that I'm grabbing from the db, and wish to insert the results into a table:

Code: Select all

$getGallery   = "SELECT * FROM gallery";
$gallery      = mysql_query($getGallery);
Then display the results into a table, with a few form items added.

::::::::: Checkbox ::::::::: Checkbox
Image1: Checkbox Image2: Checkbox
::::::::: Checkbox ::::::::: Checkbox
Image Name Image Name

What I'm not seeing, is how to insert into the table, while iterating through mysql_fetch_array. If I am iterating through the results, and am at Image1, the name result would need to be in a different row, that I wouldn't begin writing until after I had written the cells for image2.. Does that make sense? I'm struggling with how to verbalize it..

Thanks
David

Posted: Thu Feb 08, 2007 3:38 pm
by Mordred
I have a query that I'm grabbing from the db, and wish to insert the results into a table:
Read the MySQL manual on "INSERT ... SELECT"
Then display the results into a table, with a few form items added.
So, after the insert-select, you just do a regular select and display the table.

In case I didn't understand what you want,
What I'm not seeing, is how to insert into the table, while iterating through mysql_fetch_array.
Well, mysql_fetch_array() accepts a mandatory argument, $result.
Since the queries you'll be issuing from inside the loop will be different queries, there should be no problem just doing the inserts while iterating. If there is a problem (I haven't tried it, so not 100% sure), you can just do it in two steps:
1. loop mysql_fetch_array and add its result in a 2D array, representing your entire result set
2. loop through the 2D array and insert the lines you want.

The latter will almost surely not be needed, which is good, because it's "ugly" ;), issuing new queries should not invalidate the result of your select.

Posted: Thu Feb 08, 2007 3:54 pm
by dwessell
Hey Mordred,

I think that I probably explained my quandry quite badly.. It's not the inserts into the DB table, put the formatting of the HTML table.. I have a image and a name that I'm am pulling from the DB table. I want to insert them into a HTML table, along with few form widgets. (See my pretty picture above).

The image, and the image name are contained in two different rows of the HTML table.. But while I'm iterating through the result returned from the DB, I only have access to one field at a time (ie. image1 and image1name)..

If I want to put the information into a HTML table that is three images wide, I'm moving ahead to the next resource (say image2) before I would have written the image1name into table..

I know that doesn't make much sense.. i'm going to try and word it better, then come back..

I did find something similar, although not exact at (viewtopic.php?t=25105).. So at least I'm on the right track..

Thanks
David

Posted: Thu Feb 08, 2007 4:27 pm
by RobertGonzalez
This is a table formatting issue in your output. Their is some logic, but you may want to consider using CSS to handle the display as DIV's would be a whole lot better suited than trying to put this stuff into a table.

Posted: Thu Feb 08, 2007 5:57 pm
by dwessell
Everah,

Thanks for clarifying. The reason I didn't go with divs originally was twofold.. Number one, the data seemed to fit tablular data. Number two, I won't know how many results will be returned (ie, how many galleries there will be) until runtime. Thus from what I could anticipate, it seemed that it would be difficult to style with divs, at least more so then tables..

This logic you speak of.. :) Is it possible to access any part of the array returned by mysql_fetch_assoc, without affecting the pointer location?

Perhaps the best way go to about it, is to iterate through the array, dumping the contents into another array that can be manipulated more readily?

Thanks
David

Posted: Thu Feb 08, 2007 6:04 pm
by RobertGonzalez
Well, think about it. You are in a row of the result set and you want to put one piece of the data in one place, and another piece of that data in another place. Correct? So start simple, and try outputting the result set in a straight line. Then look at how the markup needs to be in order to make it the way you want. From the looks of it, you are building a wide table with image 1 data in the 1 column, image 2 data in the 2 column and so on. If you must use tables, can you put image 1 information into cell with breaks so the name is below the image?