Help with MySQL Select Statement

MySQL database
#1

I have a form that pull out 100 records from a MySQL table…then i create an INSERT statement to INSERT those records to another table

Statement:

$msg_sender = $_POST[‘msg_sender’];
$msg_recip = $_POST[‘msg_recip’];
$msg_subj = $_POST[‘msg_subj’];
$msg_text = $_POST[‘msg_text’];
$msg_time = $_POST[‘msg_time’];
$msg_ip = $_POST[‘msg_ip’];
$msg_status = $_POST[‘msg_status’];
$msg_read = $_POST[‘msg_read’];

INSERT INTO rate_messages (msg_sender,msg_recip,msg_subj,msg_text,msg_time,msg_ip,msg_status)
VALUES (’$msg_sender’,’$msg_recip’,’$msg_subj’,’$msg_text’,’$msg_time’,’$msg_ip’,’$msg_status’)";

Problem:
The statement only INSERT the last record NOT all of them. Can someone please tell me what i’m doing wrong?

#2 
$query=mysql_query("SELECT * FROM tblname",$conn);
while ($data=mysql_fetch_array($query)) {

     $msg_sender = $data['field_msg_sender'];
     $msg_recip = $data['field_msg_recip'];
     $msg_subj = $data['field_msg_subj'];
     $msg_text = $data['field_msg_text'];
     $msg_time = $data['field_msg_time'];
     $msg_ip = $data['field_msg_ip'];
     $msg_status = $data['field_msg_status'];
     $msg_read = $data['field_msg_read'];

INSERT INTO rate_messages (msg_sender,msg_recip,msg_subj,msg_text,msg_time,msg_ip,msg_status)
VALUES ('$msg_sender','$msg_recip','$msg_subj','$msg_text','$msg_time','$msg_ip','$msg_status')";

}
#3

What you gave me is not working because I pull the data using a form. I limit the select statement to 100. When i looked at the page source all the 100 records are there…all i wanna do is post those record i pull to another table (message table). I also have predefine values I want to insert to the message table. example below.

[code]
$res2 = sql_query(“SELECT * FROM memtable WHERE m_state LIKE CONVERT( _utf8 ‘Florida’ USING latin1 )
COLLATE latin1_swedish_ci AND m_city LIKE CONVERT( _utf8 ‘%orlando%’ USING latin1 )
COLLATE latin1_swedish_ci AND m_confirmed !=0 AND m_type =1 LIMIT 100”);

echo “

Blast Processing Center

”;

echo “”;
while ($row = mysql_fetch_array($res2, MYSQL_BOTH))
{
if ($i%6==0){
echo “”;
}

echo ("
Sender:
Receipient:
Subj:
Text: looking to read bout what BCF finest has to say bout me or what would like do with me, holla
Time:
IP:
Status:
Read: ");

    $i++;

    }[/code]
#4

So, you have some predefined records that you want to insert into the memtable? Sorry, I’m slightly confused with what you’re asking.

If you could, please put your code into a block, like so

Code
#5

What exactly are you trying to make? Your last code snippet shows you’re outputting 100 forms to a page. What are you intending to do? I’m thinking you’re making this a lot harder on yourself than you should be.

#6

Zyppora, you’re right. I’m trying to insert into a new table all 100 forms outputted with this code below. The only problem is it’s only inserting the last form (record)

php
$msg_sender = $row[‘msg_sender’];
$msg_recip = $row[‘msg_recip’];
$msg_subj = $row[‘msg_subj’];
$msg_text = $row[‘msg_text’];
$msg_time = $row[‘msg_time’];
$msg_ip = $row[‘msg_ip’];
$msg_status = $row[‘msg_status’];
$msg_read = $row[‘msg_read’];

$username="";
$password="";
$database="";
$conn = mysql_pconnect("$location","$username","$password");
if (!$conn) die (“Could not connect to MySQL”);
mysql_select_db($database,$conn) or die (“Could not open database”);

$query = “INSERT INTO rate_messages (msg_sender,msg_recip,msg_subj,msg_text,msg_time,msg_ip,msg_status,msg_read)
VALUES (’$msg_sender’,’$msg_recip’,’$msg_subj’,’$msg_text’,’$msg_time’,’$msg_ip’,’$msg_status’,’$msg_read’)”;

$result=mysql_query($query) or die ("Could not complete query because ".mysql_error());

mysql_close();

#7

Here is the complete code:

CODE 1
This one retrieve 100 records from a table:

Blast.php

$res2 = sql_query(“SELECT * FROM memtable WHERE m_state LIKE CONVERT( _utf8 ‘Florida’ USING latin1 )
COLLATE latin1_swedish_ci AND m_city LIKE CONVERT( _utf8 ‘%orlando%’ USING latin1 )
COLLATE latin1_swedish_ci AND m_confirmed !=0 AND m_type =1 LIMIT 100”);

echo “

Blast Processing Center

”;

echo “”;
while ($row = mysql_fetch_array($res2, MYSQL_BOTH))
{
if ($i%6==0){
echo “”;
}

echo ("
Sender:
Receipient:
Subj:
Text: looking to read bout what BCF finest has to say bout me or what would like do with me, holla
Time:
IP:
Status:
Read: ");

    $i++;

    }

CODE 2: Blast_Sent.php
This one grab the 100 records and insert them to a new table

php
$msg_sender = $row[‘msg_sender’];
$msg_recip = $row[‘msg_recip’];
$msg_subj = $row[‘msg_subj’];
$msg_text = $row[‘msg_text’];
$msg_time = $row[‘msg_time’];
$msg_ip = $row[‘msg_ip’];
$msg_status = $row[‘msg_status’];
$msg_read = $row[‘msg_read’];

$username="";
$password="";
$database="";
$conn = mysql_pconnect("$location","$username","$password");
if (!$conn) die (“Could not connect to MySQL”);
mysql_select_db($database,$conn) or die (“Could not open database”);

$query = “INSERT INTO rate_messages (msg_sender,msg_recip,msg_subj,msg_text,msg_time,msg_ip,msg_status,msg_read)
VALUES (’$msg_sender’,’$msg_recip’,’$msg_subj’,’$msg_text’,’$msg_time’,’$msg_ip’,’$msg_status’,’$msg_read’)”;

$result=mysql_query($query) or die ("Could not complete query because ".mysql_error());

mysql_close();

#8

mathieus67,

What’s the purpose of these two scripts? The first script prints off 100 forms? The second script inserts a single row of data. Also, put your code in code tags, please.

-boobay

#9

Yes boobay, the first code pull 100 forms and the second code grabs the form and insert the data into a second table. The problem is only the last record is inserted…not all 100

[code]$res2 = sql_query(“SELECT * FROM memtable WHERE m_state LIKE CONVERT( _utf8 ‘Florida’ USING latin1 )
COLLATE latin1_swedish_ci AND m_city LIKE CONVERT( _utf8 ‘%orlando%’ USING latin1 )
COLLATE latin1_swedish_ci AND m_confirmed !=0 AND m_type =1 LIMIT 100”);

echo “

Blast Processing Center

”;

echo “”;
while ($row = mysql_fetch_array($res2, MYSQL_BOTH))
{
if ($i%6==0){
echo “”;
}

echo ("
Sender:
Receipient:
Subj:
Text: looking to read bout what BCF finest has to say bout me or what would like do with me, holla
Time:
IP:
Status:
Read: ");

$i++;

}
[/code]

CODE 2: Blast_Sent.php
This one grab the 100 records and insert them to a new table

php

[code]
$msg_sender = $row[‘msg_sender’];
$msg_recip = $row[‘msg_recip’];
$msg_subj = $row[‘msg_subj’];
$msg_text = $row[‘msg_text’];
$msg_time = $row[‘msg_time’];
$msg_ip = $row[‘msg_ip’];
$msg_status = $row[‘msg_status’];
$msg_read = $row[‘msg_read’];

$username="";
$password="";
$database="";
$conn = mysql_pconnect("$location","$username","$password");
if (!$conn) die (“Could not connect to MySQL”);
mysql_select_db($database,$conn) or die (“Could not open database”);

$query = “INSERT INTO rate_messages (msg_sender,msg_recip,msg_subj,msg_text,msg_time,msg_ip,msg_status,msg_read)
VALUES (’$msg_sender’,’$msg_recip’,’$msg_subj’,’$msg_text’,’$msg_time’,’$msg_ip’,’$msg_status’,’$msg_read’)”;

$result=mysql_query($query) or die ("Could not complete query because ".mysql_error());

mysql_close();[/code]

#10

Still haven’t answered my questions, just explained what the current code does.
I can’t imagine you having a need for 100 forms on a single page, no matter what the purpose of your script is.
No matter how many forms you have on your page, you can only submit one at a time.

Can you explain to us what exactly you’re trying to do with this script?

#11

Zyppora,

I have a message and a members’ table on my MySQL database. What I would like to is:

  1. Find all the members that live in Bronx, NY (for example). Retrieve all those members and put them on a form.

  2. Take those members (they are linked to the message table a member ID) based on the member’s ID and submit a row for each of the retrieve member to the message table.

Example:

Select * FROM member_table WHERE city =“bronx”;

Save all those members on a form.

Insert into message_table (all the define fields) for the members’ ID…basically, a row will be entered with predefined fields for all the members retrieved by the select query.

I don’t know any other ways to explain it…

thanks for ya patience with me.

#12

Now I get it. What you want is extract a number of users from your members table and insert/update their values in the messages table.

<?php

echo "<form method='post' action='process.php'>";

$sql = "SELECT u.id, u.name, m.text FROM members AS u, messages AS m WHERE u.location = 'Bronx' AND u.id = m.memberid";
$rslt = mysql_query($sql);

while ($result = mysql_fetch_assoc($rslt)) {
  echo "Info for ".$result['u.name'].": <input type='text' name='text_".$result['u.id']."' value='".$result['m.text']."'><br>";
}

echo "<input type='submit' value='Submit'>";
echo "</form>";

?>
Sponsor our Newsletter | Privacy Policy | Terms of Service