Insert records from a query

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
User avatar
Lonestarjack
Forum Commoner
Posts: 31
Joined: Tue Nov 11, 2008 7:13 am
Location: Texas

Insert records from a query

Post by Lonestarjack »

I have tried different ways to populate table "p_keys" from table "programs" but to no avail.
The following code is a last resort. It does not generate any errors or records fpr that matter


My code is:

Code: Select all

<?php
$count = 0;
mysql_query('TRUNCATE TABLE p_keys;');

$result = mysql_query('SELECT distinct password FROM programs ORDER BY password');

while($row = mysql_fetch_array($result)){
	$password = $row['password'];
	$count ++;
	if(mysql_query("INSERT INTO p_keys (password) VALUES('$password')")){
		echo '<br>' . $count. '    ' . $password;
		} else {
		echo '<br>no go    ' . $password;
		};
};
?>
The results are:
no go 00179504 *
no go 04-00265272
no go 05010540
no go 07-022173
etc
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: Insert records from a query

Post by Celauran »

First of all, don't execute queries inside loops. Now, it looks like you're able to retrieve data just fine, which leaves your INSERT query as the culprit. On the surface the query looks fine. Have you tried running the query manually? Have you looked at mysql_error()?
User avatar
Mordred
DevNet Resident
Posts: 1579
Joined: Sun Sep 03, 2006 5:19 am
Location: Sofia, Bulgaria

Re: Insert records from a query

Post by Mordred »

1. INSERT ... VALUES has a syntax for adding multiple rows in a single query
2. SQL injection on $password, use mysql_real_escape_string()
3. What you really need in this case is INSERT ... SELECT
User avatar
Lonestarjack
Forum Commoner
Posts: 31
Joined: Tue Nov 11, 2008 7:13 am
Location: Texas

Re: Insert records from a query

Post by Lonestarjack »

Thanks for the suggestions I had tried the INSERT ... SELECT before without trying to catch the errors .
I ended up making p_keys a single column table, running the php code, and then adding back the columns I need.

My code:

Code: Select all

mysql_query('TRUNCATE TABLE p_keys;');
$sql = 'INSERT into p_keys SELECT distinct password  FROM programs ORDER BY password';
$result = mysql_query($sql);
if (!$result) {
die('Invalid query: ' . mysql_error());
}
User avatar
Mordred
DevNet Resident
Posts: 1579
Joined: Sun Sep 03, 2006 5:19 am
Location: Sofia, Bulgaria

Re: Insert records from a query

Post by Mordred »

Mhm, just a quick note: don't leave code like this:

die('Invalid query: ' . mysql_error());

in production code. This is a really nice thing to leave for hackers and can greatly* help them with gaining access to your database (* only if you have an SQL injection vulnerability in the query)
User avatar
Lonestarjack
Forum Commoner
Posts: 31
Joined: Tue Nov 11, 2008 7:13 am
Location: Texas

Re: Insert records from a query

Post by Lonestarjack »

Thanks I will heed your suggestion.


“Things need not have happened to be true. Tales and adventures are the shadow truths that will endure when mere facts are dust and ashes and forgotten” so spake Neil Gaiman
User avatar
Mordred
DevNet Resident
Posts: 1579
Joined: Sun Sep 03, 2006 5:19 am
Location: Sofia, Bulgaria

Re: Insert records from a query

Post by Mordred »

Yep, an my avatar is Dream, The Sandman :)
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: Insert records from a query

Post by mikosiko »

Dream has many negative aspects to his personality. He is sometimes slow when dealing with humor, occasionally insensitive, often self-obsessed, and is very slow to forgive or forget a slight. He has a long history of failed romances, and is both directly shown and implied to have reacted very harshly to some of his breakups
and hopefully it doesn't define your personality :)
User avatar
Mordred
DevNet Resident
Posts: 1579
Joined: Sun Sep 03, 2006 5:19 am
Location: Sofia, Bulgaria

Re: Insert records from a query

Post by Mordred »

Ah, but that's his past. The character portrayed in the comic is shown to grow more and more human. He goes back to fix his past mistakes, with compassion and love and a feeling for what is right which are much like our own (even though he is not human at all).

Anyway - how did we turn this topic into a literary analysis of Sandman? Must be a dream :P
temidayo
Forum Contributor
Posts: 109
Joined: Fri May 23, 2008 6:17 am
Location: Nigeria

Re: Insert records from a query

Post by temidayo »

Lonestarjack wrote:I have tried different ways to populate table "p_keys" from table "programs" but to no avail.
The following code is a last resort. It does not generate any errors or records fpr that matter


My code is:

Code: Select all

<?php
$count = 0;
mysql_query('TRUNCATE TABLE p_keys;');

$result = mysql_query('SELECT distinct password FROM programs ORDER BY password');

while($row = mysql_fetch_array($result)){
	$password = $row['password'];
	$count ++;
	if(mysql_query("INSERT INTO p_keys (password) VALUES('$password')")){
		echo '<br>' . $count. '    ' . $password;
		} else {
		echo '<br>no go    ' . $password;
		};
};
?>
The results are:
no go 00179504 *
no go 04-00265272
no go 05010540
no go 07-022173
etc
I think the probless is your choice of table field name:

Code: Select all

mysql_query("INSERT INTO p_keys (password) VALUES('$password')")
password is a reserved word for MySQL database! Usage as a field name
therefore requires some technicalities.
Post Reply