Solved

PHP SQL Add words to field in database table

Posted on 2013-11-13
4
350 Views
Last Modified: 2013-11-13
Hi,

I have a mysql table with the following fields:

id, name, story, rank

I also have 2 authors:

Peter and Paul

I need some code in php what will do the following altogether:

1st:
Select  * from table where rank = 1
Then:
From the output list I need to add the author names into the story fields.

So for example, is the result of the query above is 10 records

Then the name Peter is added to 5 records inside the story field and the name Paul is added to 5 records.

so Peter is added to 50% and Paul is added to 50%

Can anyone help please?
0
Comment
Question by:error2013
  • 3
4 Comments
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39645253
Please post the CREATE TABLE statement and the test data you're using.  It will be easy to show you how to do this once we have that starting point.
0
 

Author Comment

by:error2013
ID: 39645284
Example:

table:

  id | field2 | field3
-----------------------------
  1    hello      1
  2    hello2     1

Open in new window


String1 String2

Select * from mytable where field3 = 1

Will return 2 fields...

next...

Insert String1 into 50% of result and String2 into the other 50% of result on field2

Output should be:

  id | field2         |  field3
-----------------------------
  1    String1 hello      1
  2    String2 hello2     1

Open in new window

0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39645532
Is this a school assignment?  It seems "theoretical" to the point of "silly."  What is the back-story here that explains why you would do this?
0
 
LVL 108

Accepted Solution

by:
Ray Paseur earned 500 total points
ID: 39645614
The essential moving parts are line 185-214.  The rest is just setup and proof of concept.  Beware that if you do something like this and the length of the string in the UPDATE clause exceeds the length of the column definition, MySQL will silently truncate the data!
<?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 = "??";
require_once('RAY_live_data.php');
// 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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Generating table dynamically is the most common issue faced by php developers.... So it seems there is a need of an article that explains the basic concept of generating tables dynamically. It just requires a basic knowledge of html and little maths…
This article discusses four methods for overlaying images in a container on a web page
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.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

758 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

20 Experts available now in Live!

Get 1:1 Help Now