Page 1 of 1

moving rows from one databse to another

Posted: Fri Jun 13, 2003 2:27 pm
by CoreLEx
hi everyone,

I've got a simple script that allows users to submit their own emoticons. The submissions get added to a 'pending' database. I'd like to then be able to review the submissions and authorize those that are appropriate.


Currently, the first part of the script works great, however, I'm having trouble with the second part that retrieves the rows from the 'pending' database. Each row is displayed along with a checkbox. On pressing submit, I'd like the records that are checked to be inserted into the main database and removed from the 'pending' one.

Here is the first script that displays the contents of the main database and also allows users to make submissions:

Code: Select all

<html>
<body>
<?php

    #connect to mysql
    $connection = @mysql_connect("localhost","user","pass")
        or die("Sorry, unable to connect to MySQL");

    #select db
    $result = @mysql_select_db(asciiart,$connection)
        or die("Sorry, unable to to get database.");

#--------------
# submit data
#--------------

if($_POST&#1111;'submit'] and $title and $art)
&#123;
    #create query
    $sql="insert into pending(author,title,art) values("$author", "$title", "$art")";

    #execute query (result is bool)
    $result=mysql_query($sql,$connection);

    #success?
    if(result)&#123;
 	$url=$PHP_SELF.'?processed=' .$title;
	print "<html><head><meta http-equiv='Refresh' content='1; url=$url'></head></html>";
	die("Processing record $title");

    &#125;
    else&#123;
        echo("Record was not added successfully!");
    &#125;

&#125;

#--------------
# retrieve data
#--------------

#create query
$sql="select author,title,art from asciiart";

#execute query (result is bool)
$result=mysql_query($sql,$connection);

#write the data
echo("<center>");
echo("<table width=600 border=0 bordercolor=#666666 bgcolor=#FFFFFF style="border: 1px solid #ccd7e0; background-color: #fff;">");
echo("<tr>");

$i=0;

while($currentRow = mysql_fetch_array($result))
&#123;
    echo("<td width=20%>");
    echo("<font size="1" face="verdana">");
    echo($currentRow&#1111;"title"] ."<br>");
    echo("</font>");

    echo("<font size="2" face="verdana">");
    echo("<b>" .$currentRow&#1111;"art"] ."</b><br><br>");
    echo("</font>");
    echo("</td>");

    $i++;

    if($i==5)&#123;
        echo("</tr>");
	  echo("<tr width=100%>");
        $i=0;
    &#125;
        
&#125;

echo("</tr>");
echo("</table>");
echo("</center>");


#close
mysql_close();

?>

<center>
<form action="<? echo($PHP_SELF); ?>" method="post">
<table bordercolor="#666666" bgcolor="#FFFFFF" style="border: 1px solid #ccd7e0; background-color: #fff;">

<tr>
<td colspan=2>
<table bgcolor="#ccd7e0" border=0 width=100%>
<tr width=100%><td>
    <center><font face=verdana size=2>Submit your own art...</font></center>
</td></tr>
</table>
</td>
</tr>

<tr>
<td><font size=2 face=verdana>Your Name: </font></td>
<td><input type="text" name="author" size="18"></td>
</tr>

<tr>
<td><font size=2 face=verdana>Art Title: </font></td>
<td><input type="text" name="title" size="18"></td>
</tr>

<tr>
<td><font size=2 face=verdana>Emoticon: </font></td>
<td><input type="text" name="art" size="18"></td>
</tr>

<tr>
<td><input type="submit" name="submit" value="Submit!"></td>
</tr>

</table>
</form>
</center>

</body>
</html>
and here is the incomplete second script that acts as the admin area:

Code: Select all

<html>

<head>
<body>

<?php

    #connect to mysql
    $connection = @mysql_connect("localhost","user","pass")
        or die("Sorry, unable to connect to MySQL");

    #select db
    $result = @mysql_select_db(asciiart,$connection)
        or die("Sorry, unable to to get database.");

#--------------
# retrieve data
#--------------

#create query
$sql="select id,author,title,art from pending";

#execute query (result is bool)
$result=mysql_query($sql,$connection);

#write the data
echo("<form action="$PHP_SELF" method=post>");
echo("<center>");
echo("<table width=600 border=0 bordercolor=#666666 bgcolor=#FFFFFF style="border: 1px solid #ccd7e0; background-color: #fff;">");
echo("<tr>");

$i=0;

while($currentRow = mysql_fetch_array($result))
&#123;
    echo("<td width=20%>");
    echo("<font size="1" face="verdana">");
    echo($currentRow&#1111;"title"] ."<br>");
    echo("</font>");

    echo("<font size="2" face="verdana">");
    echo("<b>" .$currentRow&#1111;"art"] ."</b><br><br>");
    echo("</font>");
     echo("<input type=checkbox name=" .$currentRow&#1111;"id"]  ." value=off>");
    echo("</td>");

    $i++;

    if($i==5)&#123;
        echo("</tr>");
	  echo("<tr width=100%>");
        $i=0;
    &#125;
        
&#125;

echo("</tr>");
echo("</table>");
echo("</center>");
echo("<center><input type=submit name=submit value=Submit!></center>");


#close
mysql_close();

?>


<tr>
<td><td>
</tr>

</table>

</body>
</html>
I'd appreciate any input you guys might have.

Thanks in advance!

CoreLEx

Errr.....

Posted: Fri Jun 13, 2003 3:09 pm
by BDKR
I hope you're not mad at me for not looking at the code, but I have a suggestion.

Instead of having more than one database, add a field to the table that controls the state of
a submission. You could set the state to 1 meaning that it has not yet been reviewed. Once it's OK, set it too 0. There is a lot less code and overhead doing it this way.

Cheers,
BDKR

Posted: Fri Jun 13, 2003 3:18 pm
by polosport6699
How would you go about doing that Code wise?

Posted: Fri Jun 13, 2003 3:25 pm
by BDKR
polosport6699 wrote:How would you go about doing that Code wise?
We really don't write code for people around here. We help each other with issues with code and design ideas.

Cheers,
BDKR

Thanks

Posted: Fri Jun 13, 2003 6:00 pm
by CoreLEx
Thanks for the quick reply BDKR. I'll probably take your suggestion and use only one database.

I'll let you know how I get along :)

Posted: Sat Jun 14, 2003 9:02 am
by BDKR
Cool!

Posted: Sat Jun 14, 2003 6:44 pm
by CoreLEx
Quick question about checkboxes and php:

I currently generate a checkbox with the id of the pending record for each record in the form chk[id]. On submitting the form I'd like the script to know which checkboxes have been checked in order to perform certain operations on them.

I currently have the following code but it does not seem to work (all checkboxes are read as 'checked'!):

Code: Select all

$approved=0;

if($_POST&#1111;'submit'])
&#123;
    $i=0;

    while($i<$records_size)
    &#123;
        $chkBox="chk" .$records&#1111;$i];

        #if checkbox of current record's id is checked
        if($chkBox!="")
        &#123;
            #create query
            $sql="UPDATE asciiart SET approved=1 WHERE id=$records&#1111;$i]";

            #execute query
            $result=mysql_query($sql,$connection);

            $approved++;
        &#125;

    $i++;
    &#125;
        
    $url=$PHP_SELF ."?processed";
    print "<html><head><meta http-equiv='Refresh' content='1; url=$url'></head></html>";
    die("$approved of $records_size approved");
&#125;
The if($chkBox!="") part of the code is obviously what's causing the problem, but I don't know to change it. Were it a VB app I would have done if chkBox[id].checked=false :wink:

Any pointers?

Thanks.

CoreLEx

Posted: Sat Jun 14, 2003 8:16 pm
by Paddy
Use

Code: Select all

<?php
foreach ($chkbox as $chkitem)
{
      code (Do something with $chkitem)
}
?>
The foreach will take each item of a checkbox group and enter the code if it is checked.

Posted: Sun Jun 15, 2003 6:52 am
by CoreLEx
Thanks Paddy. $chkBox is not an array but rather a string that is updated on each iteration. I tried creating an array of checkbxoes and using foreach() as you suggested but I'd get the same problem (all checkboxes would be read as checked)

Sorry, I'm a bit confused. :cry:

CoreLEx