Page 1 of 1
Insert records from a query
Posted: Sun Sep 16, 2012 8:13 am
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
Re: Insert records from a query
Posted: Sun Sep 16, 2012 10:40 am
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()?
Re: Insert records from a query
Posted: Mon Sep 17, 2012 9:05 am
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
Re: Insert records from a query
Posted: Mon Sep 17, 2012 11:12 am
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());
}
Re: Insert records from a query
Posted: Tue Sep 18, 2012 5:30 am
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)
Re: Insert records from a query
Posted: Tue Sep 18, 2012 8:03 am
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
Re: Insert records from a query
Posted: Tue Sep 18, 2012 8:17 am
by Mordred
Yep, an my avatar is Dream, The Sandman

Re: Insert records from a query
Posted: Tue Sep 18, 2012 8:35 am
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

Re: Insert records from a query
Posted: Tue Sep 18, 2012 8:58 am
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

Re: Insert records from a query
Posted: Thu Sep 27, 2012 9:41 am
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.