array not coming together (sql construction)

Ninjakreborn · November 29, 2006

I was trying to construct a query.
I have a group of image's, I am needing to change the sort order of the image's dynamically from a dynamically created form. I have ran through a few ideas' but nothing is substantial,
[code]<?php
require_once("../master/config/config.php");
?>
<?php
// This page below get's a little hairy
if ($_SESSION['controller'] == true) {
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<?php
include($docroot . "/master/includes/meta.inc.php");
?>
</head>
<body id="adminbody">
<?php
// include administration header
include($docroot . "/master/includes/header.admin.inc.php");
?>
<?php
// include administration left navigation
include($docroot . "/master/includes/leftnav.admin.inc.php");
?>
<div id="adminrightcolumn">
<h1>Change Image Sort Order</h1>
<?php
if ($_POST['check'] == "yes") {
$id = array($_POST['id']);
$order = array($_POST['order']);
$update = "UPDATE oak_images SET ";
foreach ($id as $var1) {
$update .= "order =
}
dumparray($id);
echo "<br />";
dumparray($order);

}

?>

</div>
</body>
</html>
<?php
}
?>[/code]
You see here I got stuck in mid thought, what is a good way to go about doing this.
I need it to go through each id, and set the order number to the order number they choose, one's that were blank, in the database, will be automatically set to not-set. From default at the beginning, I just need it to go through and update all the image information in teh forms, to change the order numbers to the set one's.
Since there all different id's don't i need to let it construct one query for each image, which will be pretty easy, but that could end up sometime's being 20-30 different update queries, depending on how many images where changed.

Any advicewould be appreciated, mostly int he best way to go about doing this, or should i just let it write a new update query for each individual id number.

Ninjakreborn · November 29, 2006

craygo · November 29, 2006

what does your tables look like??(structure) And what exactly are you trying to do??? Can you give me some table structure and some data to work with??

ray

roopurt18 · November 29, 2006

I'm guessing that you're intending for your ID and Order arrays to be arrays of single valued entities and not arrays of arrays. The problem is that the ID and Order arrays themselves contain arrays.

[code]foreach ($id as $var1) {
$update[$var1] = "UPDATE oak_images SET order = '" . $order . "' WHERE id = '" . $var1 . "';";
}[/code]
The fact that $id contains arrays means that $var1 [b]is[/b] an array. The error you're getting is stating that your offset into the $update array is illegal, in this case it's an array.

Your code is essentially doing this, and it's illegal:
$update[Array(1,2,3,4)] = "some sql statement";

Ninjakreborn · November 29, 2006

Ok, as far as data in the table.

It's a database that was already finished, and this is just a feature I am building in, the person is wanting to be able to sort images.
I constructed the backend to the site in a week, now I am just helping my graphic designer, by oding some new features into backend, well all are done, but this.
He said theyw anted to be able to sort there image.s
I didn't want a lot of hastle, so I created 1 extra field in the images table called order
varchar, whatever
it nulls to not set or something, so at the beginning I don't accept an order number, that way I didn't have to touch the script that they originally add images with.

Now, I am at the editing page, I have a form, here is the form.

[code]<?php
require_once("../master/config/config.php");
?>
<?php
// This page below get's a little hairy
if ($_SESSION['controller'] == true) {
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<?php
include($docroot . "/master/includes/meta.inc.php");
?>
</head>
<body id="adminbody">
<?php
// include administration header
include($docroot . "/master/includes/header.admin.inc.php");
?>
<?php
// include administration left navigation
include($docroot . "/master/includes/leftnav.admin.inc.php");
?>
<div id="adminrightcolumn">
<h1>Edit Image Information</h1>
<p>Below you will find a list of every image associated with this property. You will also (below each image) see the status, whether it's enabled or disabled. THe final 2 things you will see (below enabled information) is a link to either delete that image, or disable that image. Please note if a property is disabled/deleted all image's associated with that property are deleted/disabled respectively as well. Please also note, that the text field is the sort order.</p>
<hr />
<?php
$id = mysql_real_escape_string($_GET['id']);

$property = "SELECT address1 FROM oak_properties WHERE propertynumber = '$id';";
$prop_query = mysql_query($property);

while ($prop_row = mysql_fetch_array($prop_query)) {
echo "<h1>{$prop_row['address1']}</h1>";
}

$select = "SELECT * FROM oak_images WHERE propertynumber = '$id';";
$query = mysql_query($select);
echo '<form name="form1" id="form1" action="imagesort.php" method="post" />';
while ($row = mysql_fetch_array($query)) {
echo "<div id=\"adminimages\">\n";
$height = "height=\"100px\"";
$width = "width=\"100px\"";
echo "<img src=\"/oakley/{$row[pathtofile]}{$row[filename]}\" alt=\"{$row[filename]}\" {$height} {$width} /><br /><br />";
if ($row['enabled'] == "yes") {
echo "<a href=\"disableimage.php?id={$row[imageid]}\" title=\"Disable Image\">Disable</a>\n";
}elseif ($row['enabled'] == "no") {
echo "<a href=\"enableimage.php?id={$row[imageid]}&prop={$row[propertynumber]}\" title=\"Enable Image\">Enable</a>\n";
}
echo "<br />";
if ($row['featured'] == "no") {
echo "<a href=\"featureimage.php?id={$row[imageid]}&prop={$row[propertynumber]}\" title=\"Feature Image\">Make Featured</a>\n";
}elseif ($row['featured'] == "yes") {
echo "Featured Image\n";
}
echo "<br />";
echo "<a href=\"deleteimage.php?id={$row[imageid]}\" title=\"Delete Image\">Delete</a>\n";
echo "<br />";
echo "<input name=\"id[]\" type=\"hidden\" value=\"{$row[imageid]}\" />\n";
echo "<input name=\"order[]\" type=\"text\" value=\"{$row[order]}\" size=\"5\" />\n";
echo "<br /></div>\n";
}
echo '<input name="check" id="check" type="hidden" value="yes" />' . "\n";
echo "<input name=\"submit\" id=\"submit\" type=\"submit\" value=\"change sort order\" />";
echo "</form>\n";
?>
<hr />
</div>
</body>
</html>
<?php
}
?>[/code]

The only things I did to that page, to add this new feature, was wrap the dynamic display of images in form tag's, and dynamically create input fields to retrieve the id, and order input.

THey can put in a number, but they don't have to put them all in, since later, all I will do, is order the queries by order number, so the first numbers will get displayed first.
IT's cheap, but it's what they wanted, they just wanted something basic, say, hey I want this image to appear "before" this one, so I put a a number on this one lower than the other, and they will retain that order.

That is the point of this whole thing, so I bring those 2 things over the id, and order, and I was thinking of constructing one update array for each one, and running that array through a foreach to do mysql queries on each one.

This is the only idea I came up with, since an update query can only relaly update 1 row, if you need to update different things, (in this situation updating different id's, with the new order value), then I was thinking seperate queries

That is a breakdown of hte whole situation.

Ninjakreborn · November 29, 2006

[quote]I'm guessing that you're intending for your ID and Order arrays to be arrays of single valued entities and not arrays of arrays. The problem is that the ID and Order arrays themselves contain arrays.

Code:

foreach ($id as $var1) {
$update[$var1] = "UPDATE oak_images SET order = '" . $order . "' WHERE id = '" . $var1 . "';";
}

The fact that $id contains arrays means that $var1 is an array. The error you're getting is stating that your offset into the $update array is illegal, in this case it's an array.

Your code is essentially doing this, and it's illegal:[/quote]

I am a little confused, I thought that
id was an array, and so was order
I thought the id array, contained all the id's.
of course it has it's generated key's, but then the id's
I thought the same for order, I am missing something, and getting confused?

$update[Array(1,2,3,4)] = "some sql statement";

Jocka · November 29, 2006

Humor me .. lol. Can you just change this:
echo "<input name=\"id[]\" type=\"hidden\" value=\"{$row[imageid]}\" />\n";
echo "<input name=\"order[]\" type=\"text\" value=\"{$row[order]}\" size=\"5\" />\n";

To this:
echo "<input name=\"order[$row[imageid]]\" type=\"text\" value=\"{$row[order]}\" size=\"5\" />\n";

and then in the php file to sort it:
$order = $_POST['order'];
$i = 0;
foreach($order as $id => $ord){
$update[$i] = "UPDATE oak_images SET order='" . $ord . "' WHERE id='".$id."'";
$i++;
}

then query.. ?

::EDIT:: forgot the $i++

roopurt18 · November 29, 2006

Anothing thing:
[code]$order = array($_POST['order']);
$update = array();
foreach ($id as $var1) {
$update[$var1] = "UPDATE oak_images SET order = '" . $order . "' WHERE id = '" . $var1 . "';";
}
[/code]

You declare $order as an array and then use it in your sql statement without providing a key. Your sql is always going to be:
UPDATE oak_images SET order = '[b]array[/b]' WHERE id = 'array'

You're trying to step through two arrays with a foreach. The only way that's possible is if both arrays have the same keys for the corresponding values. The fact that you don't even have a key variable being set in your foreach tells me, loud and clear, you have no idea what you're doing.

Create a new file, and stick this in it:
[code]
<?php
$arr1 = Array( 1, 2, 3, 4 );
$arr2 = Array( 'cat', 'dogs', 'horses', 'fish' );

foreach($arr1 as $key => $val){
echo "I have {$arr1[$key]} {$arr2[$key]}<br />";
}
?>[/code]

Ninjakreborn · November 29, 2006

Thanks I will go over these 2 ideas, and let you know how It went.
or ask more questions.

I won't say I don't know anything about what I am doing, I will say, that at this moment, I use to do everything the long, and hard way.

For the past few "weeks" only I have started really getting more into
Array's, Function's, File handling
those 3 things I wasn't very good at, so whenenever I encounter them (lately I force myself to use them), I make it a learning experience and work through it, I had forgotten foreach needed a key, because for some reason, lalely without the extra part, all the other things I was trying to do with an array, worked with foreach, just using
foreach ($array as $val)
or whatever, they were working exactly as expected, so I had forgotten that was needed.

EDIT - again it didn't let me post for a minute, so here is an update
It worked, the first thing you gave me, (Jocka)
thank you, and roopurt thanks for the advice, I will let you know how the rest of it works out, and if it all works out from here, I will mark it as solved.

Also the
++ thinga t the bottom was required for it to work, it broke when I took that out, I am working up the rest, and I will tell you how it goes, and marked as solved if it all works out properly.

roopurt18 · November 29, 2006

"I had forgotten foreach needed a key, because for some reason, lalely without the extra part, all the other things I was trying to do with an array, worked with foreach, just using
foreach ($array as $val)
or whatever, they were working exactly as expected, so I had forgotten that was needed."

That's just it. It's [b]not always[/b] needed. Whether or not you include a $key value in your foreach is dependent on what you're trying to accomplish.

Ninjakreborn · November 29, 2006

Now it make's a lot more sense.
I will do some more checking on the different uses, thanks, I appreciate it. I will try this out, if everything run's smoothly i will mark it as solved, if not I will ask other questions in this same post.

roopurt18 · November 29, 2006

I noticed you said your order column for the images was a VARCHAR, I highly recommend changing this to INT or you'll have problems later.

Ninjakreborn · November 29, 2006

Why, I understand where the problem's could arrise.
But, it the database it defaults over to not set, that way it's just ignored in the ordering process, I need a way to be able to order the one's with the higher numbers before the lower numbers, but any "not set" will just be set at the bottom, in any random order.

This was the point of varchar whatever, it is set to default at notset
that way it knows they haven't chosen a number, I figured those get pushed at the bottom, the one's they do set, will have a number, so it can order it by that number, unless it will work out properly if they are all a number but default to 0 or something, I don'tk now, any advice?

roopurt18 · November 29, 2006

INT and VARCHAR columns do not sort the same. Since you are specifically writing this bit of code for sorting you might want to choose a datatype that sorts correctly for the type of data you will most likely be giving it.

Sign In

array not coming together (sql construction)

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information