moving rows from one databse to another

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
CoreLEx
Forum Newbie
Posts: 10
Joined: Sun Jun 01, 2003 5:18 pm

moving rows from one databse to another

Post 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
User avatar
BDKR
DevNet Resident
Posts: 1207
Joined: Sat Jun 08, 2002 1:24 pm
Location: Florida
Contact:

Errr.....

Post 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
polosport6699
Forum Commoner
Posts: 35
Joined: Wed Jun 11, 2003 3:19 pm

Post by polosport6699 »

How would you go about doing that Code wise?
User avatar
BDKR
DevNet Resident
Posts: 1207
Joined: Sat Jun 08, 2002 1:24 pm
Location: Florida
Contact:

Post 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
CoreLEx
Forum Newbie
Posts: 10
Joined: Sun Jun 01, 2003 5:18 pm

Thanks

Post 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 :)
User avatar
BDKR
DevNet Resident
Posts: 1207
Joined: Sat Jun 08, 2002 1:24 pm
Location: Florida
Contact:

Post by BDKR »

Cool!
CoreLEx
Forum Newbie
Posts: 10
Joined: Sun Jun 01, 2003 5:18 pm

Post 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
Paddy
Forum Contributor
Posts: 244
Joined: Wed Jun 11, 2003 8:16 pm
Location: Hobart, Tas, Aussie
Contact:

Post 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.
CoreLEx
Forum Newbie
Posts: 10
Joined: Sun Jun 01, 2003 5:18 pm

Post 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
Post Reply