PHP Code not working ... Assistance needed

Hi all,

I am trying to run this code and it's currently returning no errors but it's not moving any records:

Here is the full code:

<?php
$mysqli = new mysqli("localhost", "username", "password", "mydb");

/* check connection */
if (mysqli_connect_errno()) {
    echo("Connect failed.");
    exit();
}

$query = "SELECT id FROM table1 WHERE siteid = 1 LIMIT 1";

if ($result = $mysqli->query($query)) {

    /* fetch object array */
    while ($row = $result->fetch_row()) {
        $query = "INSERT INTO table2 SELECT s.* FROM table1 s WHERE id=" . $row[1] . ";";
        $mysqli->query($query);
        $query = "DELETE FROM table1 WHERE id=" . $row[1] . ";";
        $mysqli->query($query);
    }

    /* free result set */
    $result->close();
}

/* close connection */
$mysqli->close();
?>

Open in new window


There are 2 tables.

Fields are:

id, name and siteid

There are 3 records in it and it's just not moving anything from table1 to table2

Can anyone help?

Thanks
LVL 1
error2013Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

edster9999Commented:
A couple of things I do not like about the code -

You reference row[1]... it would be far better to reference it by name $row['id']
From memory row[1] is wrong anyway.  The numbering of the fields starts at 0 so unless you have another field to the left of 'id' then you are really comparing the number to 'name' and hence getting no data.  I think this is your issue.

You have a loop to repeat for all records that match but in the query above you have 'limit 1' so there would only ever be one (or zero) rows anyway - this is just a minor gripe - this is not stopping it work.

I also do not like the way you use 'query' inside a loop controlled by 'query'
This means you update the string inside and then come back next cycle to the top one and it can be messed up by then. Again from memory this will not throw PHP5 but it is bad practice (IMO) so use 'query' on the top one and 'updatequery' on the other two.

If all else fails insert some echo and print_r statements then you can see what is happening.

how about inserting in line 13 (and 25 to see it if you do not get into the loop)
print_r ($result)
0
error2013Author Commented:
Can't get it to work.

Can anyone provide some code fixes in code please or suggestions in code? It's urgent :o/

Thanks
0
Chris StanyonWebDevCommented:
Throw some checks and error reporting in there to make sure it's going according to plan. Run this and see how you get on:

<?php
error_reporting(E_ALL);
ini_set('display_errors', 1);

$mysqli = new mysqli("localhost", "username", "password", "mydb");

if (mysqli_connect_errno()) {
    echo("Connect failed.");
    exit();
}

$query = "SELECT id FROM table1 WHERE siteid = 1 LIMIT 1";
$result = $mysqli->query($query) or die("There was a problem running your Query");

if ($result->num_rows) {
	//we got something back
	$row = $result->fetch_assoc();

	$stmt = $mysqli->prepare("INSERT INTO table2 SELECT s.* FROM table1 s WHERE id=?");
	$stmt->bind_param("i", $row['id']);
	$stmt->execute();
	
	$stmt = $mysqli->prepare("DELETE FROM table1 WHERE id=?");
	$stmt->bind_param("i", $row['id']);
	$stmt->execute();
} else {
	echo "No records were selected";
}

$mysqli->close();
?>

Open in new window

0
Angular Fundamentals

Learn the fundamentals of Angular 2, a JavaScript framework for developing dynamic single page applications.

GaryCommented:
You don't really need a loop, you can do a direct copy and delete
Amend col1, etc to your table columns
INSERT INTO table2 (col1,col2,col3)
SELECT col1,col2,col3 FROM table1 WHERE siteid=1

Open in new window

DELETE FROM table1 WHERE siteid=1

Open in new window

http://dev.mysql.com/doc/refman/5.0/en/ansi-diff-select-into-table.html
0
error2013Author Commented:
Almost perfect!

Only one thing not working...

When I change the LIMIT for for example 3  ... If it finds 3 matches it should move the 3 but it still just moving 1 record at the time.
0
error2013Author Commented:
Anyone please?
0
Chris StanyonWebDevCommented:
If you up the LIMIT then you need to loop:

$query = "SELECT id FROM table1 WHERE siteid = 1 LIMIT 3";
$result = $mysqli->query($query) or die("There was a problem running your Query");

if ($result->num_rows) {
	//we got something back
	while ($row = $result->fetch_assoc()):
	   $stmt = $mysqli->prepare("INSERT INTO table2 SELECT s.* FROM table1 s WHERE id=?");
   	   $stmt->bind_param("i", $row['id']);
   	   $stmt->execute();
	
   	   $stmt = $mysqli->prepare("DELETE FROM table1 WHERE id=?");
   	   $stmt->bind_param("i", $row['id']);
   	   $stmt->execute();
	endwhile;
} else {
	echo "No records were selected";
}

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
error2013Author Commented:
Thanks :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.