Solved

PHP Code modification needed (Variables in loop)

Posted on 2013-11-14
1
293 Views
Last Modified: 2013-11-14
Hi all,

First of all this is my current code:

<?php

$conn = mysqli_connect('localhost', 'admin', '', 'test');
$rs = mysqli_query($conn, "select count(*) cnt from table where field3 = 8");
$row = mysqli_fetch_assoc($rs);

$cnt = $row['cnt'];
$middle = ceil($cnt/2);

$var1 = 'string1';
$var2 = 'string2';

mysqli_query(
    $conn, 
    "update table a,
           (SELECT @rownum:=0) b
     set article = concat(article, if((@rownum := @rownum+1) > $middle, '$var1', '$var2'))     
     where field3 = 8
    "
); 

?>

Open in new window



This code above works fine but right now:

$var1 = 'string1';
$var2 = 'string2';

Open in new window


populate everything 50% 50%.

My problem here is that if the result on cnt is 10 (for example)...

At the moment 5 records will be populated with 'string1' and the other 5 records with 'string2'...

Now...

My problem is that $var1 and $var2 are going to be generated on the fly so $var1 and $var2 will be different on every run instead of now....

So it should be like this::;;

//If cnt was 10 for example...

$var1 = 'string1a';
$var2 = 'string2a';

Open in new window


....

update the database ...with those 2 variables above...

//then generate another 2 values...

$var1 = 'string1b';
$var2 = 'string2b';

Open in new window


update the database ...with those 2 variables above...

etc until it's reached cnt.

Note: I don't need help with generating the different $var values, I just need to have the code store different ones in a loop.

Thanks
0
Comment
Question by:error2013
1 Comment
 
LVL 108

Accepted Solution

by:
Ray Paseur earned 500 total points
ID: 39647999
Is this a classroom assignment?  Why are you doing this?  I know of no design pattern like this.  If you can explain the business rules that you're trying to implement, we may be better able to help you.

To refresh memory, here is yesterday's answer.  Perhaps we can build on that?

<?php // RAY_temp_error2013.php
ini_set('display_errors', TRUE);
error_reporting(E_ALL);
echo '<pre>';


// THIS SCRIPT DEMONSTRATES SOME OF THE BASICS OF MySQLi


// THE ABSOLUTE MINIMUM YOU MUST UNDERSTAND TO USE PHP AND MYSQLI
// MAN PAGE: http://php.net/manual/en/mysqli.overview.php
// MAN PAGE: http://php.net/manual/en/class.mysqli.php
// MAN PAGE: http://php.net/manual/en/class.mysqli-stmt.php
// MAN PAGE: http://php.net/manual/en/class.mysqli-result.php
// MAN PAGE: http://php.net/manual/en/class.mysqli-warning.php
// MAN PAGE: http://php.net/manual/en/class.mysqli-sql-exception.php <-- DID NOT WORK PHP 5.3+, MySQL 5.1+
// MAN PAGE: http://php.net/manual/en/mysqli.construct.php
// MAN PAGE: http://php.net/manual/en/mysqli.real-escape-string.php
// MAN PAGE: http://php.net/manual/en/mysqli.query.php
// MAN PAGE: http://php.net/manual/en/mysqli.errno.php
// MAN PAGE: http://php.net/manual/en/mysqli.error.php
// MAN PAGE: http://php.net/manual/en/mysqli.insert-id.php
// MAN PAGE: http://php.net/manual/en/mysqli-result.num-rows.php
// MAN PAGE: http://php.net/manual/en/mysqli-result.fetch-array.php
// MAN PAGE: http://php.net/manual/en/mysqli-result.fetch-object.php


// CREATE AN ARRAY OF NAMES TO USE FOR TEST DATA
$test_data_arrays = array
( array( "field2" => "hello"  , "field3" => "1" )
, array( "field2" => "hello2" , "field3" => "1" )
)
;


// DATABASE CONNECTION AND SELECTION VARIABLES - GET THESE FROM YOUR HOSTING COMPANY
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";
$db_user = "??";
$db_word = "??";

// OPEN A CONNECTION TO THE DATA BASE SERVER AND SELECT THE DB
$mysqli = new mysqli($db_host, $db_user, $db_word, $db_name);

// DID THE CONNECT/SELECT WORK OR FAIL?
if ($mysqli->connect_errno)
{
    $err
    = "CONNECT FAIL: "
    . $mysqli->connect_errno
    . ' '
    . $mysqli->connect_error
    ;
    trigger_error($err, E_USER_ERROR);
}

// ACTIVATE THIS TO SHOW WHAT THE DB CONNECTION OBJECT LOOKS LIKE
// var_dump($mysqli);


// CREATING A TABLE FOR OUR TEST DATA
$sql
=
"
CREATE TEMPORARY TABLE my_table
( id    INT         NOT NULL AUTO_INCREMENT PRIMARY KEY
, field2 VARCHAR(24) NOT NULL DEFAULT ''
, field3 VARCHAR(24) NOT NULL DEFAULT ''
)
"
;

// RUN THE QUERY TO CREATE THE TABLE
$res = $mysqli->query($sql);

// IF mysqli::query() RETURNS FALSE, LOG AND SHOW THE ERROR
if (!$res)
{
    $err
    = 'QUERY FAILURE:'
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    . ' QUERY: '
    . $sql
    ;
    trigger_error($err, E_USER_ERROR);
}

// SHOW THE RESULTS OF THE QUERY
var_dump($res);


// LOADING OUR DATA INTO THE TABLE
foreach ($test_data_arrays as $thing)
{
    // ESCAPE THE DATA FOR SAFE USE IN A QUERY
    $safe_f2  = $mysqli->real_escape_string($thing['field2']);
    $safe_f3  = $mysqli->real_escape_string($thing['field3']);

    // CONSTRUCT THE QUERY USING THE ESCAPED VARIABLES
    $sql = "INSERT INTO my_table ( field2, field3 ) VALUES ( '$safe_f2', '$safe_f3' )";

    // RUN THE QUERY TO INSERT THE ROW
    $res = $mysqli->query($sql);

    // IF mysqli::query() RETURNS FALSE, LOG AND SHOW THE ERROR
    if (!$res)
    {
        $err
        = 'QUERY FAILURE:'
        . ' ERRNO: '
        . $mysqli->errno
        . ' ERROR: '
        . $mysqli->error
        . ' QUERY: '
        . $sql
        ;
        trigger_error($err, E_USER_ERROR);
    }


    // GET THE AUTO_INCREMENT ID OF THE RECORD JUST INSERTED
    $id  = $mysqli->insert_id;
    echo "MySQLI INSERTED A ROW CONTAINING <b>$safe_f2 $safe_f3</b> WITH AUTO_INCREMENT ID = $id" . PHP_EOL;
}
echo PHP_EOL;


// CREATE AND SEND A SELECT QUERY AND TEST THE RESULTS
$sql = "SELECT id, field2, field3 FROM my_table ORDER BY id";
$res = $mysqli->query($sql);

// IF mysqli_query() RETURNS FALSE, LOG AND SHOW THE ERROR
if (!$res)
{
    $err
    = 'QUERY FAILURE:'
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    . ' QUERY: '
    . $sql
    ;
    trigger_error($err, E_USER_ERROR);
}
// IF WE GET THIS FAR, THE QUERY SUCCEEDED AND WE HAVE A RESULT OBJECT IN $res
// AND SO WE CAN NOW USE $res IN OTHER MYSQLI FUNCTIONS


// DETERMINE HOW MANY ROWS OF RESULTS WE GOT
$num     = $res->num_rows;
$num_fmt = number_format($num);
if (!$num)
{
    echo "<br/>QUERY: $sql ";
    echo "<br/>FOUND NO DATA ";
    echo PHP_EOL;
}
else
{
    echo "<br/>QUERY: $sql ";
    echo "<br/>FOUND $num_fmt ROWS OF DATA ";
    echo PHP_EOL;
}
echo PHP_EOL;


// ITERATE OVER THE RESULTS SET AS AN OBJECT TO SHOW WHAT WE FOUND
echo PHP_EOL . 'USING MySQLi_Result::Fetch_<i>Object</i>(): ';
echo PHP_EOL;
while ($row = $res->fetch_object())
{
    // ROW BY ROW PROCESSING IS DONE HERE
    print_r($row);
    echo PHP_EOL;
}
echo PHP_EOL;

// RESET THE RESULTS OBJECT
$res->data_seek(0);

// THINGS TO INSERT
$stuff = array('String1', 'String2');

// A POINTER TO THE THINGS
$point = 1;

// ITERATE OVER THE ROWS TO INSERT THE DATA INTO THE COLUMNS
while ($row = $res->fetch_object())
{
    $point = $point + 1;
    $point = $point % 2;
    $newf2 = $stuff[$point] . ' ' . $row->field2;
    $updat = "UPDATE my_table SET field2 = '$newf2' WHERE id = $row->id LIMIT 1";
    $upres = $mysqli->query($updat);

    // IF mysqli_query() RETURNS FALSE, LOG AND SHOW THE ERROR
    if (!$upres)
    {
        $err
        = 'QUERY FAILURE:'
        . ' ERRNO: '
        . $mysqli->errno
        . ' ERROR: '
        . $mysqli->error
        . ' QUERY: '
        . $updat
        ;
        trigger_error($err, E_USER_ERROR);
    }
}


// SHOW THE UPDATED TABLE
$sql = "SELECT id, field2, field3 FROM my_table ORDER BY id";
$res = $mysqli->query($sql);

// IF mysqli_query() RETURNS FALSE, LOG AND SHOW THE ERROR
if (!$res)
{
    $err
    = 'QUERY FAILURE:'
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    . ' QUERY: '
    . $sql
    ;
    trigger_error($err, E_USER_ERROR);
}
// IF WE GET THIS FAR, THE QUERY SUCCEEDED AND WE HAVE A RESULT OBJECT IN $res
// AND SO WE CAN NOW USE $res IN OTHER MYSQLI FUNCTIONS


// DETERMINE HOW MANY ROWS OF RESULTS WE GOT
$num     = $res->num_rows;
$num_fmt = number_format($num);
if (!$num)
{
    echo "<br/>QUERY: $sql ";
    echo "<br/>FOUND NO DATA ";
    echo PHP_EOL;
}
else
{
    echo "<br/>QUERY: $sql ";
    echo "<br/>FOUND $num_fmt ROWS OF DATA ";
    echo PHP_EOL;
}
echo PHP_EOL;


// ITERATE OVER THE RESULTS SET AS AN OBJECT TO SHOW WHAT WE FOUND
echo PHP_EOL . 'USING MySQLi_Result::Fetch_<i>Object</i>(): ';
echo PHP_EOL;
while ($row = $res->fetch_object())
{
    // ROW BY ROW PROCESSING IS DONE HERE
    print_r($row);
    echo PHP_EOL;
}
echo PHP_EOL;

Open in new window

0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
The viewer will learn how to dynamically set the form action using jQuery.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

930 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now