Re: Storing array in a row



guitarromantic@xxxxxxxxx wrote:
Jerry Stuckle wrote:


Well, you can also get the name of all values from the $_POST array, i.e.

foreach ($_POST as $key->$value) {


Ahh, thanks again. One last question:

When I post the final values to my database, I need to split them up.
Say I enter 3 authors and 3 individual scores and reviews, I need to
save them concurrently. What I've just done is coded a query to insert
the data that applies to all the multiple-author reviews (eg, band
name, record label, etc) into the main reviews table. I also assigned
that row a "review_type" of '3' which tells me it's a multiple review.
What I wanna do to display a review is test for type, if "3", then
search in table multiple_reviews for all occurences of whatever $id
review I'm displaying.

After my code to insert these common variables into the db, I then pull
out the most recent $id entered into the reviews table, and store that
as the $id for the new row being saved in multiple_reviews, with the
intention of storing like so:

table: reviews

id: 1234
artist_name: The Beatles
album_name: Let It Be
record_label: Apple
author_id: (left blank)
review: (left blank)
score: (left blank)
review_type: 3

table: multiple reviews

id: 1234
author_id: 34
score: 7
review: blah blah

id: 1234
author_id: 67
score: 8
review: blah blah BLAH

etc. I hope that wasn't too insulting to your intelligence..

Anyway, my issue is, for the INSERT to get that info into the second
table, how can I grab each element of the array? As you've probably
gathered, I'm new to php (I could probably do this in Java, though..)
and all I've got so far is:

foreach ($author as $value) {
echo "$value";
}

which just prints out each element of the array. How can I make an
insert query that'll populate the tables as outlined above (with the
last three rows mentioned all being arrays)?


First of all, don't store review_type. You can find out if it has multiple reviews by a simple

SELECT COUNT(*) FROM multip_reviews WHERE id='1234'

If the count is > 1, you have multiple reviews. Otherwise you need to ensure you keep this in sync. For instance, if you later add a review, you need to check to see what the current review_type is (it might be zero or 1 review) then update it.

As for inserting multiple values, it's pretty simple. You need to build the query in your foreach loop. First you need to get all of your variables for a single review into an array, i.e. assuming you have your form fields as author1, author2, score1, score2 and review1, review2, etc.: (warning - not tested)

$data = array();
foreach($_POST as $key->$value) {
if (substr($key, 0, 6) == 'author')
$data[int(substr($key,6))]['author'] = $value;
elseif (substr($key, 0, 5) == 'score')
$data[int(substr($key,5))]['score'] = $value;
elseif (substr($key, 0, 6) == 'review')
$data[int(substr($key,5))]['review'] = $value;
}

Your results will now be in $data[1]['author'], $data[1]['score'], $data[1]['review'], etc.

Now that the data is sorted, you can build an insert VALUES clause ($id has the review id)

$insert = '';
foreach($data as $value) {
if ($insert != '') // add a comma if not the first one
$insert .= ', ';
$insert .= "($id, $value['author'], $value['score'], '$value['review'])";
}

And finally you can insert into the table:

$query = "INSERT INTO review_table(id, author_id, score, review) VALUES $insert;
$result = mysql_query($query);

Not perfect, and I didn't do basic error checking (i.e. ensure all the values are filled in), but hopefully you get the idea.


--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@xxxxxxxxxxxxx
==================
.



Relevant Pages