[RESOLVED] Set my variables please

Peceiver

I have a query that i want to send out to my db and the issue is with setting the query statement.
The script requires that a unknown number of queries be created during a transaction.

To create the queries I have used a 'For loop', but have failed in executing the queries, as of this post, through mysql_query.

ERROR: Invalid query: Query was empty

<?php
// connect to mysql
mysql_connect('blah.db.godaddy.com', 'blah', 'H^T#Py^pZjY6UCtTs$AtQkBeHn', 'blablah');

$quantity = 3; // yet undetermined variable that sets the number of queries to create, set for the test

// looping sql insert statement
for( $i = 1; $i < $quantity; $i++)
{
$passwd = sha1(rand(25, 32));
$sql[] = "INSERT INTO `clients` ( `text0` , `text1` , `text2` , `status_all` , `status_1` , `status_2` , `status_3` ) VALUES (" . $txn_id . $i . ", " . $passwd . ",  blah, 0, " . $status_1. ", " . $status_2. ", " . $status_3. ");";
}

$$query1 = $sql[0];
$$query2 = $sql[1];
$$query3 = $sql[2];

// execute sql
$result = mysql_query($query1);
if (!$result) {
    die('Invalid query: ' . mysql_error());
}
$result = mysql_query($query2);
if (!$result) {
    die('Invalid query: ' . mysql_error());
}
$result = mysql_query($query3);
if (!$result) {
    die('Invalid query: ' . mysql_error());
}

?>

Please advise, will you.

bradgrafelman

Few issues I see...

Don't create an array of queries when a single query will do. Note that a single query isn't limited to INSERT'ing a single row... example:
```
INSERT INTO myTable (col1, col2)
VALUES
    ('row1-col1', 'row1-col2'),
    ('row2-col1', 'row2-col2'),
    # etc. ad nauseum
    ('rowN-col1', 'rowN-col2')
;
```
In other words, simply create a single query $sql and just append tuples onto the end of the query (e.g. after VALUES as shown above).

Of course, if you plan on doing a large amount of rows at once then you might be better of splitting them into chunks so that you don't hit MySQL's max query size limit.
This:
```
$$query1 = $sql[0]; 
$$query2 = $sql[1]; 
$$query3 = $sql[2]; 
```
doesn't make any sense, since a) there's no reason you should be using variable variables, and b) you aren't using variable variables correctly (where are $query1, $query2, and $query3 ever defined before those lines?).

This point is moot, of course, assuming you consider the above advise.
Based on the column names in your query, I'm guessing that your database schema fails even the first normal form (might want to Google 'database normalization' and read a few good articles on the topic).

This might be fine with you if you're just throwing together some sort of test environment (although I've personally never been a fan of learning/doing something the wrong way for any reason).
$txn_id, $status_1, $status_2, $status_3 are all undefined variables.

laserlight

Please post PHP code in [noparse]

[/noparse] bbcode tags.

There is no need to use variable variables here, so stop using them.

Although using an array is more palatable, there is no need to use an array either. Rather, execute the SQL statements within the body of the loop.

Note that your loop is not looping the correct number of times: you should either start with $i = 0; then compare $i < $quantity, or start with $i = 1; then compare $i <= $quantity.

Also, I recommend doing something like this when constructing the SQL statement:

$sql = sprintf("INSERT INTO `clients` (`text1`) VALUES ('&#37;s')",
               mysql_real_escape_string($passwd));

You do not actually need mysql_real_escape_string here because sha1 was used, but this is a general pattern.

Better yet, switch to the MySQLi extension or the PDO extension is used prepared statements.

Peceiver

I use the variable variables were pointless, thanks.
I am still getting an interesting error;

ERROR: Invalid query: Query was empty

// looping sql insert statement
for( $i = 1; $i < $quantity; $i++)  ///////////<<<<< needs to be reworked <see PHPBuilders forums>
{
  // create pass
  $passwd = sha1(rand(18589, 986322));
  // set sql
  $sql[] = "INSERT INTO `clients` ( `username` , `passwd` , `groups` , `status_all` , `status_bpmn` , `status_pmbok` , `status_babok` ) VALUES ( `username`, `passwd`, `groups`, `0`, `0`, `1`, `1`);";
  // execute sql
  $result = mysql_query($sql[$i]);
  if (!$result) {
      die('Invalid query: ' . mysql_error());
  }
}

bradgrafelman

Array indexes start at 0, whereas your loop starts at 1. Thus, $sql[] is always going to be creating the index $i-1 inside the loop, yet you're trying to access the (nonexistent) next element at $i.

Peceiver

// looping sql insert statement
for( $i = 0; $i < $quantity; $i++){

// set other variables
$txn_id = 'txn_id';
$status_p= '0';
$status_b= '0';
$status_i= '0';

// create pass
$passwd = sha1(rand(18589, 986322));
// set sql
$sql[$i] = "INSERT INTO clients ( id, username, passwd, groups, status_all, status_p, status_i, status_b) VALUES (id,username" . $txn_id . $i . "," . $passwd . ",paypal,0," . $status_p. "," . $status_i. "," . $status_b. ");";
// execute sql
$result = mysql_query($sql[$i]);
if (!$result) {
die('Invalid query: ' . mysql_error());
echo $sql[$i] . "<br/>";
}
}

How does the above code generate the following error?

ERROR: Invalid query: Unknown column 'usernametxn_id0' in 'field list'
... its referring to the VALUES ..
Edit/Delete Message

bradgrafelman

Strings in SQL queries must be delimited in some way (e.g. single quotes, according to standard SQL) just as they must be in PHP... otherwise they're treated as identifiers (e.g. table columns).

Peceiver

You rock, you rock, you rock, you rock, you rock, you rock, you rock, and finally you rock!

Peceiver

ERROR: Invalid query: Duplicate entry '0' for key 1

// looping sql insert statement
for( $i = 0; $i < $quantity; $i++){

// set other variables
$txn_id = 'txn_id';
$status_1= '0';
$status_2= '0';
$status_3= '0';

// create pass
$passwd = sha1(rand(18589, 986322));
// set sql
$sql[$i] = "INSERT INTO clients ( id, username, passwd, groups, status_all, status_1, status_2, status_3) VALUES ('id','username" . $txn_id . $i . "','" . $passwd . "','paypal','0','0','1','1');";
// execute sql
$result = mysql_query($sql[$i]);
if (!$result) {
die('Invalid query: ' . mysql_error());
echo $sql[$i] . "<br/>";
}
}

ERROR: Invalid query: Duplicate entry '0' for key 1
Edit/Delete Message

johanafm

"INSERT INTO clients (id, username, passwd, groups, status_all, status_1, status_2, status_3)
VALUES ('id','username" . $txn_id . $i . "','" . $passwd . "','paypal','0','0','1','1');";

Well, look at the value you're trying to insert as clients.id, the string literal 'id'. Then look at the table definitiion, e.g. with

SHOW CREATE TABLE clients

and you are likely to find that id is an unsigned integer, which means that 'id' has to be converted from string to integer, and since the string doesn't contain leading numerals, it will be converted to 0.

A primary key must always be unique within its table, and the first time you insert 0 into clients.id, it is. The second time you insert 0, it's not, thus

duplicate entry '0'

bradgrafelman

Also note that if 'id' is an AUTO_INCREMENT column, then the best course of action would be to remove it from the query altogether and let MySQL take care of it.