Storing array in a row

[guitarromantic]

Hey everyone,

I run a site with staff-submitted reviews, and most of them are written
by one author. However, we also do "multiple" reviews. Up until now I
just had a userid for a 'Multiple' account and submitted them under
that, but this makes it harder to print lists of all the reviews by one
person, so ideally I wanna make a multiple select form so we can just
select all the contributors and have the values saved in the database -
in one row (something like "56,34,21" etc).

I've coded a multiple select form with all the authors of articles for
my site on, and I want to store the results of this in one row so I can
grab them as an array later - how can I go about doing this? I just
googled and came across using <select name="author[]"> in my HTML, and
then using serialize() to process it, but it fails. When I run the
following code, the value for 'multiplearray' remains at 0 whether I
select single OR multiple values on the form:

<?php
$dbhost = ###
$dbuser = ###
$dbpass = ###
$dbname = ###

$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die ('Error
connecting to mysql');
mysql_select_db($dbname);

if(isset($_POST['save']))
{

$author = serialize($_POST['author']);

$query = "INSERT INTO review_test (multiplearray) VALUES
('$author')";
mysql_query($query) or die('Error ,query failed');
echo "Query successful!";
}

echo "<form name='test' method='post' action=''>";

$query2 = "SELECT Staff_id, displayname FROM users WHERE
userlevel !=0
ORDER BY username";
$result2 = mysql_query($query2) or die('Error : ' .
mysql_error());

echo "<select name='author[]' id='author[]' multiple='multiple'
size='10'>";
while($row2 = mysql_fetch_array($result2, MYSQL_NUM))
{
list($Staff_id, $displayname) = $row2;
echo "<option value='$Staff_id'>$displayname</option>";
}

echo "</select>";
echo "<br><br><input name='save' type='submit' id='save' value='Post
Review'>";
echo "</form>";

?>
-----

Matt

[Rik]

gu************@gmail.com wrote:

I wanna make a multiple select form
so we can just select all the contributors and have the values saved
in the database - in one row (something like "56,34,21" etc).

$author = implode(',',$_POST['author']);

And if you'd need to extract it all again:

$authors = explode(',',$author);
(assuming there is no ',' in the $Staff_id)

Allthough, if it becomes a common habit, maybe an extra table will be
preferred. It would make handling data on the database side somewhat easier.

If this doesn't work:
1. Did you deribately set action='' in your form?
2. print_r($_POST), does your $_POST['author'] contain the values?

Grtz,
--
Rik Wasmus

[guitarromantic]

Rik wrote:

$author = implode(',',$_POST['author']);

And if you'd need to extract it all again:

$authors = explode(',',$author);
(assuming there is no ',' in the $Staff_id)
Thankyou for this, very helpful.
Allthough, if it becomes a common habit, maybe an extra table will be
preferred. It would make handling data on the database side somewhat easier.
Out of a database of about 780 reviews, 65 are multiple authors. I
think it'd be more of a headache to work the other way (plus I can't
really get my head around how I'd have to restructure the database).
If this doesn't work:
1. Did you deribately set action='' in your form?
2. print_r($_POST), does your $_POST['author'] contain the values?

1. Nope, bad coding on my part. Removed.
2. (It didn't work) Yeah, it's got the values, and the one that ends up
in the db is author[0]. Is there anything special I need to do on the
database field for this data? I just have it as a standard int
currently.

Matt

[guitarromantic]

Oops, just occured to me that I can't store a comma in an int field..
changed to varchar, worked a charm, thanks!

Do you think this approach is still viable for my site, though? If
there's a more efficient/professional way, I'm interested.

[Jerry Stuckle]

gu************@gmail.com wrote:

Hey everyone,

I run a site with staff-submitted reviews, and most of them are written
by one author. However, we also do "multiple" reviews. Up until now I
just had a userid for a 'Multiple' account and submitted them under
that, but this makes it harder to print lists of all the reviews by one
person, so ideally I wanna make a multiple select form so we can just
select all the contributors and have the values saved in the database -
in one row (something like "56,34,21" etc).

I've coded a multiple select form with all the authors of articles for
my site on, and I want to store the results of this in one row so I can
grab them as an array later - how can I go about doing this? I just
googled and came across using <select name="author[]"> in my HTML, and
then using serialize() to process it, but it fails. When I run the
following code, the value for 'multiplearray' remains at 0 whether I
select single OR multiple values on the form:

<?php
$dbhost = ###
$dbuser = ###
$dbpass = ###
$dbname = ###

$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die ('Error
connecting to mysql');
mysql_select_db($dbname);

if(isset($_POST['save']))
{

$author = serialize($_POST['author']);

$query = "INSERT INTO review_test (multiplearray) VALUES
('$author')";
mysql_query($query) or die('Error ,query failed');
echo "Query successful!";
}

echo "<form name='test' method='post' action=''>";

$query2 = "SELECT Staff_id, displayname FROM users WHERE
userlevel !=0
ORDER BY username";
$result2 = mysql_query($query2) or die('Error : ' .
mysql_error());

echo "<select name='author[]' id='author[]' multiple='multiple'
size='10'>";
while($row2 = mysql_fetch_array($result2, MYSQL_NUM))
{
list($Staff_id, $displayname) = $row2;
echo "<option value='$Staff_id'>$displayname</option>";
}

echo "</select>";
echo "<br><br><input name='save' type='submit' id='save' value='Post
Review'>";
echo "</form>";

?>
-----

Matt

Matt,

Another table is definitely the way to go. Conistst of two columns - testid and
reviewerid - plus anything else you might have specific to that reviewer on that
test.

Do some searches on "Database Normalization". It will definitely help you much
more in the long run.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================

[Rik]

gu************@gmail.com wrote:

Oops, just occured to me that I can't store a comma in an int field..
changed to varchar, worked a charm, thanks!

Do you think this approach is still viable for my site, though? If
there's a more efficient/professional way, I'm interested.

An extra table would help to make the data more accessable.

TABLE reviews
review_id
some_other_fields

TABLE staff
staff_id
some_other_fields

TABLE staff_reviews
review_id
staff_id

That way cou can also keep the id's all integers, which would be a lot
faster then searching a varchar for a number that may or may nog be between
comma's.

To keep integrity intact I'd advice using InnoDB tables:
http://dev.mysql.com/doc/refman/5.0/...nstraints.html
Unless there is a lot of adding and deleting, then using InnoDB might slow
it down.

Grtz,
--
Rik Wasmus

[guitarromantic]

Rik wrote:

An extra table would help to make the data more accessable.

TABLE reviews
review_id
some_other_fields

TABLE staff
staff_id
some_other_fields

TABLE staff_reviews
review_id
staff_id

Just to clarify - so when I display a review, would I have to then
search staff_reviews for $foo (review id) and then select all the
associated staff_ids to then display my links and stuff?

I think this is a better method also because as well as each review
having multiple authors, it has multiple scores too (currently we just
write the individual scores in the body of the review and store the
average score in the db - with this system I can store a score for each
review in staff_reviews and then just use php to display the average,
right?

[Good Man]

Jerry Stuckle <js*******@attglobal.net> wrote in
news:Md********************@comcast.com:

gu************@gmail.com wrote:

Hey everyone,

I run a site with staff-submitted reviews, and most of them are
written by one author. However, we also do "multiple" reviews. Up
until now I just had a userid for a 'Multiple' account and submitted
them under that, but this makes it harder to print lists of all the
reviews by one person, so ideally I wanna make a multiple select form
so we can just select all the contributors and have the values saved
in the database - in one row (something like "56,34,21" etc).

Another table is definitely the way to go. Conistst of two columns -
testid and reviewerid - plus anything else you might have specific to
that reviewer on that test.

Triple recommended. Basically, follow this rule:

If the data you want to store is a list of values seperated by commas (or
any other delimiter), 99% of the time it is ALWAYS better to create
another table (a many-to-many table).

[guitarromantic]

Good Man wrote:

Triple recommended. Basically, follow this rule:

If the data you want to store is a list of values seperated by commas (or
any other delimiter), 99% of the time it is ALWAYS better to create
another table (a many-to-many table).

Thank you. So is my previous post a correct assumption?

[Rik]

gu************@gmail.com wrote:

Rik wrote:

An extra table would help to make the data more accessable.

TABLE reviews
review_id
some_other_fields

TABLE staff
staff_id
some_other_fields

TABLE staff_reviews
review_id
staff_id

Just to clarify - so when I display a review, would I have to then
search staff_reviews for $foo (review id) and then select all the
associated staff_ids to then display my links and stuff?

I think this is a better method also because as well as each review
having multiple authors, it has multiple scores too (currently we just
write the individual scores in the body of the review and store the
average score in the db - with this system I can store a score for
each review in staff_reviews and then just use php to display the
average, right?

Yup, just add the review_score field to staff_revies. You could even let
MySQL take care of the average.

Grtz,
--
Rik Wasmus

[Jerry Stuckle]

gu************@gmail.com wrote:

Rik wrote:

An extra table would help to make the data more accessable.

TABLE reviews
review_id
some_other_fields

TABLE staff
staff_id
some_other_fields

TABLE staff_reviews
review_id
staff_id

Just to clarify - so when I display a review, would I have to then
search staff_reviews for $foo (review id) and then select all the
associated staff_ids to then display my links and stuff?

I think this is a better method also because as well as each review
having multiple authors, it has multiple scores too (currently we just
write the individual scores in the body of the review and store the
average score in the db - with this system I can store a score for each
review in staff_reviews and then just use php to display the average,
right?

Yes, you would. But it will be much easier and more flexible than trying to
keep a list of comma separated values.

And think of the future. For instance, what if you want to add a staff member
to a review? In your method, you have to read the current comma separated list,
add the reviewer to the end and put it back. Or if you mistakenly added one and
need to delete it, you have to read, remove and rewrite. With a separate table
it's a matter of insert or delete, and that's all.

Also, what if you want to find a list of all articles reviewed by a certain
staff member? Much more difficult with your setup.

When you have a multi-multi link like this, it's virtually certain a separate
table is the way to go.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================

[guitarromantic]

Thanks for the tips everyone, I've been experimenting.

Following this tutorial:
http://www.zend.com/zend/spotlight/c...lery-wade7.php

I've made a several-step form to simplify the submissions.

Page 1: User selects the relevant authors from a list box.
Page 2: A form is populated for each item in the author array, like so:

"Review for author $author"
[textarea for review]

I then pass these values to the final page (as the Zend tutorial
suggests, with the fieldforwarder class). My issue then is that I want
to use their code but I'm using an array within an array:

foreach ($author as $value) {
echo "Review for author $value<br>\n";
echo "<textarea name=\"review_$value\" cols=\"32\"
rows=\"6\"></textarea><br>\n";
}

The code the Zend tutorial gives for the final page is like this:

$review = htmlentities($_POST['cust_name']);

How can I grab the values for the reviews placed in the textarea using
this method?

[Jerry Stuckle]

gu************@gmail.com wrote:

Thanks for the tips everyone, I've been experimenting.

Following this tutorial:
http://www.zend.com/zend/spotlight/c...lery-wade7.php

I've made a several-step form to simplify the submissions.

Page 1: User selects the relevant authors from a list box.
Page 2: A form is populated for each item in the author array, like so:

"Review for author $author"
[textarea for review]

I then pass these values to the final page (as the Zend tutorial
suggests, with the fieldforwarder class). My issue then is that I want
to use their code but I'm using an array within an array:

foreach ($author as $value) {
echo "Review for author $value<br>\n";
echo "<textarea name=\"review_$value\" cols=\"32\"
rows=\"6\"></textarea><br>\n";
}

The code the Zend tutorial gives for the final page is like this:

$review = htmlentities($_POST['cust_name']);

How can I grab the values for the reviews placed in the textarea using
this method?

Just like Zend tells you.

Whatever is the name of your text area becomes the key to the $_POST array. In
their case they had an input field named 'cust_name'.

Since you (probably) have multiple text areas, you can either give them unique
names such as 'comment_1', "comment_2', etc. Or, a simpler approach would be to
name them all 'comment[]', in which case the $_POST['comment'] will be an array
with one element for each text area.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================

[guitarromantic]

Jerry Stuckle wrote:

Since you (probably) have multiple text areas, you can either give them unique
names such as 'comment_1', "comment_2', etc. Or, a simpler approach would be to
name them all 'comment[]', in which case the $_POST['comment'] will be an array
with one element for each text area.

Ahh, I'd given them all unique names with the name of the author_id
attached, so I was wondering how to get that. I didn't think I could
give them all the same name, thanks for enlightening me!

[Jerry Stuckle]

gu************@gmail.com wrote:

Jerry Stuckle wrote:

Since you (probably) have multiple text areas, you can either give them unique
names such as 'comment_1', "comment_2', etc. Or, a simpler approach would be to
name them all 'comment[]', in which case the $_POST['comment'] will be an array
with one element for each text area.

Ahh, I'd given them all unique names with the name of the author_id
attached, so I was wondering how to get that. I didn't think I could
give them all the same name, thanks for enlightening me!

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

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

Or name then "text1", "text2", etc. and have another hidden variable with each
one such as "staff1", "staff2", etc. which contains the staff id.

Lots of ways to do it.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================

[guitarromantic]

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)?

[Jerry Stuckle]

gu************@gmail.com 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.
js*******@attglobal.net
==================

[guitarromantic]

Jerry Stuckle wrote:

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'
My reason for using review_type is that we already have a secondary
review type (in this case it uses the same database structure, only the
review body is part of our "200 words or less" feature, and I like to
mark that visually on the review index).

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)
Apologies if this is a stupid question, but would this method only
allow 2 authors to be used when adding a review? Occasionally we use 3,
there's no single number. Just so I can get my head around this, what
are the numbers 5 and 6 doing in that code?
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'])";

Using this code I get a "Parse error: syntax error, unexpected
T_ENCAPSED_AND_WHITESPACE, expecting T_STRING or T_VARIABLE or
T_NUM_STRING" on the final line of the above code. I took out the ' by
$value, same result.

Thanks again for all the help thus far, I'll have to credit you
somewhere on the site (scenepointblank.com) when this eventually goes
live!

Matt

[Jerry Stuckle]

gu************@gmail.com wrote:

Jerry Stuckle wrote:

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'

My reason for using review_type is that we already have a secondary
review type (in this case it uses the same database structure, only the
review body is part of our "200 words or less" feature, and I like to
mark that visually on the review index).

Hmmm, it still isn't a good idea to duplicate information. What happens if for
some reason it gets out of synchronization? It does happen, for one reason or
another, like someone modifying the code and not realizing what needs to be
changed. Then you'll have other problems. And you can still mark it visually
in the review index.

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)

Apologies if this is a stupid question, but would this method only
allow 2 authors to be used when adding a review? Occasionally we use 3,
there's no single number. Just so I can get my head around this, what
are the numbers 5 and 6 doing in that code?

No, you just continue your numbering mechanism as many as you want. However, an
alternative is to enter one review per page, submit it and come back to the same
page for the next review. Then you don't have to worry about how many reviews
to enter. But if you do submit several per page you'll need to validate the
input (which you really need to do anyway).

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'])";

Using this code I get a "Parse error: syntax error, unexpected
T_ENCAPSED_AND_WHITESPACE, expecting T_STRING or T_VARIABLE or
T_NUM_STRING" on the final line of the above code. I took out the ' by
$value, same result.

As I said - untested and not guaranteed. And I don't guarantee its valid. But
the problem is there should be another single quote after the last right square
bracket.
Thanks again for all the help thus far, I'll have to credit you
somewhere on the site (scenepointblank.com) when this eventually goes
live!

Matt

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================

[guitarromantic]

Jerry Stuckle wrote:

As I said - untested and not guaranteed. And I don't guarantee its valid. But
the problem is there should be another single quote after the last right square
bracket.

Sorry, I don't mean to sound like I expect everything to work 100%.
That said, adding the missing ' didn't change the error message.

[Jerry Stuckle]

gu************@gmail.com wrote:

Jerry Stuckle wrote:

As I said - untested and not guaranteed. And I don't guarantee its valid. But
the problem is there should be another single quote after the last right square
bracket.

Sorry, I don't mean to sound like I expect everything to work 100%.
That said, adding the missing ' didn't change the error message.

Well, I never was good at matching quotes and parens. And I shouldn't have
tried to get fancy. The following should work:

$insert .= "($id, " . $value['author'] . ', ' .
$value['score'] . ", '" . $value['review'] . "')";

And BTW you'll need a double quote (") before the semicolon on the $query= line.

Sorry.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================

[guitarromantic]

Jerry Stuckle wrote:

Well, I never was good at matching quotes and parens. And I shouldn't have
tried to get fancy. The following should work:
Yeah, it's messing with my head just trying to look at that line..

And BTW you'll need a double quote (") before the semicolon on the $query= line.

Added this, but the row isn't inserted. I changed it to:
$result = mysql_query($query) or die ("error"); and it printed the
message, so there's still something wrong there. I took a look but
can't make head or tail of it, haha.

[Jerry Stuckle]

gu************@gmail.com wrote:

Jerry Stuckle wrote:

Well, I never was good at matching quotes and parens. And I shouldn't have
tried to get fancy. The following should work:

Yeah, it's messing with my head just trying to look at that line..

And BTW you'll need a double quote (") before the semicolon on the $query= line.

Added this, but the row isn't inserted. I changed it to:
$result = mysql_query($query) or die ("error"); and it printed the
message, so there's still something wrong there. I took a look but
can't make head or tail of it, haha.

Well, I don't have your tables; this was representative statements only. They
are meant to give you an idea how they work, not be your final code. You will
need to modify them as necessary to fit your circumstances.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================