Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.
Moderator: General Moderators
Sindarin
Forum Regular
Posts: 521 Joined: Tue Sep 25, 2007 8:36 am
Location: Greece
Post
by Sindarin » Tue Nov 25, 2008 9:11 am
I have a list of entries of posts that will appear on the homepage,
detailed:
The Remove link works okay. The problem is with the ReSort link. I don't know how to get each textarea, it's ID and submit them one by one in the DB.
The user is supposed to be able to use the textareas and then push the ReSort link to update the entries in the database to appear in the new order.
This is the above form,
Code: Select all
<?php
$checkvalue=0;
$query = "SELECT
*
FROM
articles
WHERE
article_showhome=1
AND
(
article_expirehome=0
OR
(
article_expire_date>CURDATE() OR article_expire_date=CURDATE()
AND
article_expire_time>CURTIME() OR article_expire_time=CURTIME()
)
)
ORDER BY
article_importance ASC
LIMIT
10";
$result = mysql_query($query) or die('Error in Query: ' . mysql_error());
echo "<form id='item' name='item' method='post' action='index.php?section=articles&action=resorthomepage'> <table width='750' border='0' cellpadding='2' cellspacing='2' style='text-indent:4px;'>
<tr>
<td height='12' align='center' valign='middle'>Order</td>
<td align='left' valign='middle'>Title</td>
<td align='left' valign='middle'>Publish Date </td>
<td align='left' valign='middle'>Expiry Date </td>
<td align='left' valign='middle'>Options </td>
</tr>";
while($row = mysql_fetch_array($result)){
$get_id=$row['article_id'];
$get_title=$row['article_title'];
$get_content=$row['article_content'];
$get_date=$row['article_publish_date'];
$get_time=$row['article_publish_time'];
$get_tags=$row['article_tags'];
$get_shownews=$row['article_shownews'];
$get_previewimage=$row['article_previewimage'];
$get_contentimage=$row['article_contentimage'];
$get_redirect=$row['article_redirect'];
$get_showhome=$row['article_showhome'];
$get_expirehome=$row['article_expirehome'];
$get_showrss=$row['article_showrss'];
$get_importance=$row['article_importance'];
$get_expire_date=$row['article_expire_date'];
$get_expire_time=$row['article_expire_time'];
$checkvalue=$checkvalue+1;
echo "
<tr onMouseOver='this.style.backgroundColor=\"#F3FE76\"' onMouseOut='this.style.backgroundColor=\"transparent\"'>
<td width='51' height='26' align='center' valign='middle'><input name='ordervalue$checkvalue' type='text' value='$get_importance' size='4' maxlength='2' /></td>
<td width='288' align='left' valign='middle'>$get_title</td>
<td width='233' align='left' valign='middle'>$get_date - $get_time</td>
<td width='233' align='left' valign='middle'>";
if ($get_expirehome==0){echo "Never";}else{ echo"$get_expire_date - $get_expire_time";}
echo "<td width='230' align='left' valign='middle'><img src='files/images/layout/delete.png' alt='remove' /><a href='index.php?section=articles&action=removehomepage&id=$get_id'>Remove</a>
<input style='display:none;' type='hidden' name='itemid' value='$get_id' /></td>
</td>
</tr>";
}
//end of entry table
echo "
</table><br/>
<div align='left' style='text-indent:8px;'><img src='files/images/layout/refresh.png' alt='resort' /><a href='javascript:document.item.submit();'> ReSort</a></div>
</form>
<br/><img src='files/images/layout/separator.png' alt='separator' />
";
//end of entry list
?>
how can I get and update/re-sort all the entries of the form regardless of their current sorting?
aceconcepts
DevNet Resident
Posts: 1424 Joined: Mon Feb 06, 2006 11:26 am
Location: London
Post
by aceconcepts » Tue Nov 25, 2008 11:25 am
Why not the field as an array using "[]" after its name?
Sindarin
Forum Regular
Posts: 521 Joined: Tue Sep 25, 2007 8:36 am
Location: Greece
Post
by Sindarin » Wed Nov 26, 2008 5:34 am
Oh, and how and I implement this? (I get confused with arrays)
Sindarin
Forum Regular
Posts: 521 Joined: Tue Sep 25, 2007 8:36 am
Location: Greece
Post
by Sindarin » Tue Dec 02, 2008 9:01 am
I am deeply confused. The loop confused me, what do I put in there? How I execute the query for each textarea and update by its id?
Sindarin
Forum Regular
Posts: 521 Joined: Tue Sep 25, 2007 8:36 am
Location: Greece
Post
by Sindarin » Wed Dec 03, 2008 3:30 am
Code: Select all
<?php
$query = "SELECT
*
FROM
articles
WHERE
article_showhome=1
AND
(
article_expirehome=0
OR
(
article_expire_date>CURDATE() OR article_expire_date=CURDATE()
AND
article_expire_time>CURTIME() OR article_expire_time=CURTIME()
)
)
ORDER BY
article_importance ASC
LIMIT
10";
$result = mysql_query($query) or die('Error in Query: ' . mysql_error());
echo "<form id='item' name='item' method='post' action='index.php?section=articles&action=resorthomepage'> <table width='750' border='0' cellpadding='2' cellspacing='2' style='text-indent:4px;'>
<tr>
<td height='12' align='center' valign='middle'>Order</td>
<td align='left' valign='middle'>Title</td>
<td align='left' valign='middle'>Publish Date </td>
<td align='left' valign='middle'>Expiry Date </td>
<td align='left' valign='middle'>Options </td>
</tr>";
while($row = mysql_fetch_array($result)){
$get_id=$row['article_id'];
$get_title=$row['article_title'];
$get_content=$row['article_content'];
$get_date=$row['article_publish_date'];
$get_time=$row['article_publish_time'];
$get_showhome=$row['article_showhome'];
$get_expirehome=$row['article_expirehome'];
$get_showrss=$row['article_showrss'];
$get_importance=$row['article_importance'];
$get_expire_date=$row['article_expire_date'];
$get_expire_time=$row['article_expire_time'];
$checkvalue=$checkvalue+1;
echo "
<tr onMouseOver='this.style.backgroundColor=\"#F3FE76\"' onMouseOut='this.style.backgroundColor=\"transparent\"'>
<td width='51' height='26' align='center' valign='middle'><input name='ordervalue[]' type='text' value='$get_importance' size='4' maxlength='2' /></td>
<td width='288' align='left' valign='middle'>$get_title</td>
<td width='233' align='left' valign='middle'>$get_date - $get_time</td>
<td width='233' align='left' valign='middle'>";
if ($get_expirehome==0){echo "Never";}else{ echo"$get_expire_date - $get_expire_time";}
echo "<td width='230' align='left' valign='middle'><img src='files/images/layout/delete.png' alt='remove' /><a href='index.php?section=articles&action=removehomepage&id=$get_id'>Remove</a>
<input style='display:none;' type='hidden' name='itemid[]' value='$get_id' /></td>
</td>
</tr>";
}
//end of entry table
echo "
</table><br/>
<div align='left' style='text-indent:8px;'><img src='files/images/layout/refresh.png' alt='resort' /><a href='javascript:document.item.submit();'> ReSort</a></div>
</form>
<br/><img src='files/images/layout/separator.png' alt='separator' />
";
//end of entry list
?>
So right now I must have two arrays ordervalue[] and itemid[], right? But I get confused at the loop, this one I tried does not work:
Code: Select all
//re-sort homepage news
$array_id=$_POST[itemid];
$array_value=$_POST[ordervalue];
for($x=0; $x<count($_POST['itemid']); $x++)
{
$db_update="UPDATE articles SET article_importance=$array_value WHERE article_id=$array_id";
mysql_query($db_update,$db_connection);
}
require_once('articles-homeform.php');
echo "done updated article with id $array_id and order value $array_value";
break;
it outputs:
done updated article with id Array and order value Array
Sindarin
Forum Regular
Posts: 521 Joined: Tue Sep 25, 2007 8:36 am
Location: Greece
Post
by Sindarin » Fri Dec 05, 2008 5:21 am
Can someone please help me on this?
VladSun
DevNet Master
Posts: 4313 Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria
Post
by VladSun » Fri Dec 05, 2008 8:09 am
Sindarin wrote: it outputs: done updated article with id Array and order value Array
$array_id, $array_value are arrays ...
There are 10 types of people in this world, those who understand binary and those who don't
Sindarin
Forum Regular
Posts: 521 Joined: Tue Sep 25, 2007 8:36 am
Location: Greece
Post
by Sindarin » Fri Dec 05, 2008 9:47 am
I mean it just outputs 'Array' and not values, plus nothing gets into the database.
VladSun
DevNet Master
Posts: 4313 Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria
Post
by VladSun » Fri Dec 05, 2008 9:50 am
Sindarin wrote:
Code: Select all
//re-sort homepage news
$array_id=$_POST[itemid];
$array_value=$_POST[ordervalue];
for($x=0; $x<count($_POST['itemid']); $x++)
{
$db_update="UPDATE articles SET article_importance=$array_value WHERE article_id=$array_id";
mysql_query($db_update,$db_connection);
}
require_once('articles-homeform.php');
echo "done updated article with id $array_id and order value $array_value";
break;
it outputs:
done updated article with id Array and order value Array
Look at your SQL query - I repeat "$array_id, $array_value are arrays ..."
There are 10 types of people in this world, those who understand binary and those who don't
Sindarin
Forum Regular
Posts: 521 Joined: Tue Sep 25, 2007 8:36 am
Location: Greece
Post
by Sindarin » Mon Dec 15, 2008 7:44 am
I tried rebuilding the code to this:
Code: Select all
//re-sort homepage news
$array_id=$_POST[itemid];
$array_value=$_POST[ordervalue];
for($x=0; $x<count($_POST['itemid']); $x++)
{
$db_update="UPDATE articles SET article_importance=$array_value[ordervalue] WHERE article_id=$array_id[$x]";
$result=mysql_query($db_update,$db_connection);
echo "done updated article with id ".$array_id[$x]." and order value ".$array_value[ordervalue]." <br/>";
}
require_once('articles-homeform.php');
break;
But it doesn't work either. Nothing gets updated at all.
VladSun
DevNet Master
Posts: 4313 Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria
Post
by VladSun » Mon Dec 15, 2008 8:02 am
Debug
Code: Select all
$result=mysql_query($db_update,$db_connection) or die(mysql_error());
There are 10 types of people in this world, those who understand binary and those who don't
Sindarin
Forum Regular
Posts: 521 Joined: Tue Sep 25, 2007 8:36 am
Location: Greece
Post
by Sindarin » Mon Dec 15, 2008 8:11 am
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE article_id=2' at line 1
Urgh, I quit, I can't do it.
I don't understand what I have to do. The arrays are there. The query is there.
I'll just do it the traditional way. I don't have enough time for all these errors.
VladSun
DevNet Master
Posts: 4313 Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria
Post
by VladSun » Mon Dec 15, 2008 8:55 am
Take a closer look at your query:
Code: Select all
echo $db_update;
$result=mysql_query($db_update,$db_connection) or die(mysql_error());
There are 10 types of people in this world, those who understand binary and those who don't
Sindarin
Forum Regular
Posts: 521 Joined: Tue Sep 25, 2007 8:36 am
Location: Greece
Post
by Sindarin » Tue Dec 16, 2008 4:05 am
Code: Select all
//re-sort homepage news
$array_id=$_POST[itemid];
$array_value=$_POST[ordervalue];
for($x=0; $x<count($_POST['itemid']); $x++)
{
$db_update="UPDATE articles SET article_importance='$array_value[$x]' WHERE article_id='$array_id[$x]'";
$result=mysql_query($db_update,$db_connection) or die(mysql_error());
}
Finally, is done!!
*insert Ogreish cry here*