How to update all re-sortable values of a form in a database

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

User avatar
Sindarin
Forum Regular
Posts: 521
Joined: Tue Sep 25, 2007 8:36 am
Location: Greece

How to update all re-sortable values of a form in a database

Post by Sindarin »

I have a list of entries of posts that will appear on the homepage,

Image
detailed:
Image

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&#058;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?
User avatar
aceconcepts
DevNet Resident
Posts: 1424
Joined: Mon Feb 06, 2006 11:26 am
Location: London

Re: How to update all re-sortable values of a form in a database

Post by aceconcepts »

Why not the field as an array using "[]" after its name?
User avatar
Sindarin
Forum Regular
Posts: 521
Joined: Tue Sep 25, 2007 8:36 am
Location: Greece

Re: How to update all re-sortable values of a form in a database

Post by Sindarin »

Oh, and how and I implement this? (I get confused with arrays)
User avatar
aceconcepts
DevNet Resident
Posts: 1424
Joined: Mon Feb 06, 2006 11:26 am
Location: London

Re: How to update all re-sortable values of a form in a database

Post by aceconcepts »

Take a look at this earlier post: viewtopic.php?f=1&t=91264
User avatar
Sindarin
Forum Regular
Posts: 521
Joined: Tue Sep 25, 2007 8:36 am
Location: Greece

Re: How to update all re-sortable values of a form in a database

Post by Sindarin »

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?
User avatar
Sindarin
Forum Regular
Posts: 521
Joined: Tue Sep 25, 2007 8:36 am
Location: Greece

Re: How to update all re-sortable values of a form in a database

Post by Sindarin »

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&#058;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
User avatar
Sindarin
Forum Regular
Posts: 521
Joined: Tue Sep 25, 2007 8:36 am
Location: Greece

Re: How to update all re-sortable values of a form in a database

Post by Sindarin »

Can someone please help me on this?
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: How to update all re-sortable values of a form in a database

Post by VladSun »

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
User avatar
Sindarin
Forum Regular
Posts: 521
Joined: Tue Sep 25, 2007 8:36 am
Location: Greece

Re: How to update all re-sortable values of a form in a database

Post by Sindarin »

I mean it just outputs 'Array' and not values, plus nothing gets into the database.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: How to update all re-sortable values of a form in a database

Post by VladSun »

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
User avatar
Sindarin
Forum Regular
Posts: 521
Joined: Tue Sep 25, 2007 8:36 am
Location: Greece

Re: How to update all re-sortable values of a form in a database

Post by Sindarin »

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.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: How to update all re-sortable values of a form in a database

Post by VladSun »

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
User avatar
Sindarin
Forum Regular
Posts: 521
Joined: Tue Sep 25, 2007 8:36 am
Location: Greece

Re: How to update all re-sortable values of a form in a database

Post by Sindarin »

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. :banghead: 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.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: How to update all re-sortable values of a form in a database

Post by VladSun »

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
User avatar
Sindarin
Forum Regular
Posts: 521
Joined: Tue Sep 25, 2007 8:36 am
Location: Greece

Re: How to update all re-sortable values of a form in a database

Post by Sindarin »

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!! 8O *insert Ogreish cry here* :twisted:
Post Reply