Error in fetching array

[rippergr]

Hello

I have the following code

<?php


// STORE DATABASE VARIABLES
$hostname_cnConnection = "localhost";
$database_cnConnection = "tekou";
$username_cnConnection = "root";
$password_cnConnection = "1234";
$cnConnection = mysql_pconnect($hostname_cnConnection, $username_cnConnection, $password_cnConnection);
mysql_set_charset('utf8',$cnConnection);
// CONNECT TO DATABASE
mysql_select_db($database_cnConnection, $cnConnection);


$data = mysql_query("SELECT * from jos_users") 

or die(mysql_error()); 
// Print "<table border cellpadding=3>"; 
while($info = mysql_fetch_array( $data )) 
{ 
// echo $info['id'];
  $prod_id=$info['id'] ;
//  echo "Product ID " .$prod_id; //Show Product Id
  
mysql_query("UPDATE jos_vm_user_info SET first_name = '".$info['name']." WHERE user_id = ".$info['id']) or die(mysql_error());  


  }
echo $info['id'];

?> 

 

I want to read every $name and update in jos_vm_user_info the first_name but I think I have error in code because nothing happens.

I quess that .$info['id'] doesnt return a single number but all numbers but I dont know how to fix it.

Please someone to help me.

[Psycho]

You are going about this the wrong way. You do not need to run a SELECT query then loop over each record to update them individually. Just run a single UPDATE query to process all the records. I wouldn't even run it in PHP, just open PHPMyAdmin (or whatever console you have for your database) and run the following query. At least I assume this is supposed to be a one-time operation.

 

UPDATE jos_vm_user_info SET first_name = name

 

This will update every record such that the 'first_name' field will be updated with the value that exists in the 'name' field for every record.

[rippergr]

Thanks for your reply

Can you tell me how can I get the =name which is in jos_users table field name

 

[spiderwell]

the statement sets the first_name field value to that of the name field value, you dont need to get it

[AGuyWithAthing]

You are going about this the wrong way. You do not need to run a SELECT query then loop over each record to update them individually. Just run a single UPDATE query to process all the records. I wouldn't even run it in PHP, just open PHPMyAdmin (or whatever console you have for your database) and run the following query. At least I assume this is supposed to be a one-time operation.

 

UPDATE jos_vm_user_info SET first_name = name

 

This will update every record such that the 'first_name' field will be updated with the value that exists in the 'name' field for every record.

 

That's not going to do much as jos_users and jos_vm_user_info are separate tables.

 

But, he is right just do it with an update join :

 

 UPDATE FROM jos_vm_user_info AS _info JOIN jos_users AS _users ON ( _info.user_id = _users.`id` ) SET _info.first_name = _users.`name`; 

 

think I got that right?

[Psycho]

Thanks for your reply

Can you tell me how can I get the =name which is in jos_users table field name

 

Oh, I didn't see that those two queries were from two different tables. No matter, you can still run just one query. I'm just not sure of the exact syntax. I'll post back shortly with a solution.

 

EDIT: Here you go

UPDATE jos_vm_user_info AS i
SET i.first_name = (SELECT u.name
                    FROM jos_users AS u
                    WHERE u.id = i.user_id)

[AGuyWithAthing]

Thanks for your reply

Can you tell me how can I get the =name which is in jos_users table field name

 

Oh, I didn't see that those two queries were from two different tables. No matter, you can still run just one query. I'm just not sure of the exact syntax. I'll post back shortly with a solution.

 

EDIT: Here you go

UPDATE jos_vm_user_info AS i
SET i.first_name = (SELECT u.name
                    FROM jos_users AS u
                    WHERE u.id = i.user_id)

 

Subqueries should never be used when you can do a join as subquery will be executed on every iteration of the query where as a join will only execute the once.

[Psycho]

Subqueries should never be used when you can do a join as subquery will be executed on every iteration of the query where as a join will only execute the once.

Then show the query you would use. This should be for a one-time operation so I wasn't interested in efficiency.

[AGuyWithAthing]

Subqueries should never be used when you can do a join as subquery will be executed on every iteration of the query where as a join will only execute the once.

Then show the query you would use. This should be for a one-time operation so I wasn't interested in efficiency.

 

I did above ^^

[Psycho]

Subqueries should never be used when you can do a join as subquery will be executed on every iteration of the query where as a join will only execute the once.

Then show the query you would use. This should be for a one-time operation so I wasn't interested in efficiency.

 

I did above ^^

 

But, why didn't you display it in a manner that I wouldn't miss it and make a fool of myself. How rude.

[rippergr]

Thanks for your reply

Can you tell me how can I get the =name which is in jos_users table field name

 

Oh, I didn't see that those two queries were from two different tables. No matter, you can still run just one query. I'm just not sure of the exact syntax. I'll post back shortly with a solution.

 

EDIT: Here you go

UPDATE jos_vm_user_info AS i
SET i.first_name = (SELECT u.name
                    FROM jos_users AS u
                    WHERE u.id = i.user_id)

 

 

Thanks.

This code works but it only fills the last row . I have almost 1000 rows .

[Psycho]

Hmm . . . I ran it on two tables I set up to mimic your setup and it updated all the records accordingly.

[rippergr]

Let me give you all the tables structure maybe I didnt explain it very well.

I attach the two tables maybe this will help.

 

CREATE TABLE IF NOT EXISTS `jos_vm_user_info` (
  `user_info_id` varchar(32) NOT NULL DEFAULT '',
  `user_id` int(11) NOT NULL DEFAULT '0',
  `address_type` char(2) DEFAULT NULL,
  `address_type_name` varchar(32) DEFAULT NULL,
  `company` varchar(64) DEFAULT NULL,
  `title` varchar(32) DEFAULT NULL,
  `last_name` varchar(32) DEFAULT NULL,
  `first_name` varchar(32) DEFAULT NULL,
  `middle_name` varchar(32) DEFAULT NULL,
  `phone_1` varchar(32) DEFAULT NULL,
  `phone_2` varchar(32) DEFAULT NULL,
  `fax` varchar(32) DEFAULT NULL,
  `address_1` varchar(64) NOT NULL DEFAULT '',
  `address_2` varchar(64) DEFAULT NULL,
  `city` varchar(32) NOT NULL DEFAULT '',
  `state` varchar(32) NOT NULL DEFAULT '',
  `country` varchar(32) NOT NULL DEFAULT 'US',
  `zip` varchar(32) NOT NULL DEFAULT '',
  `user_email` varchar(255) DEFAULT NULL,
  `extra_field_1` varchar(255) DEFAULT NULL,
  `extra_field_2` varchar(255) DEFAULT NULL,
  `extra_field_3` varchar(255) DEFAULT NULL,
  `extra_field_4` char(1) DEFAULT NULL,
  `extra_field_5` char(1) DEFAULT NULL,
  `cdate` int(11) DEFAULT NULL,
  `mdate` int(11) DEFAULT NULL,
  `perms` varchar(40) NOT NULL DEFAULT 'shopper',
  `bank_account_nr` varchar(32) NOT NULL DEFAULT '',
  `bank_name` varchar(32) NOT NULL DEFAULT '',
  `bank_sort_code` varchar(16) NOT NULL DEFAULT '',
  `bank_iban` varchar(64) NOT NULL DEFAULT '',
  `bank_account_holder` varchar(48) NOT NULL DEFAULT '',
  `bank_account_type` enum('Checking','Business Checking','Savings') NOT NULL DEFAULT 'Checking',
  PRIMARY KEY (`user_info_id`),
  KEY `idx_user_info_user_id` (`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Customer Information, BT = BillTo and ST = ShipTo';

 

and 

 

CREATE TABLE IF NOT EXISTS `jos_users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL DEFAULT '',
  `username` varchar(150) NOT NULL DEFAULT '',
  `email` varchar(100) NOT NULL DEFAULT '',
  `password` varchar(100) NOT NULL DEFAULT '',
  `usertype` varchar(25) NOT NULL DEFAULT '',
  `block` tinyint(4) NOT NULL DEFAULT '0',
  `sendEmail` tinyint(4) DEFAULT '0',
  `gid` tinyint(3) unsigned NOT NULL DEFAULT '1',
  `registerDate` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `lastvisitDate` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `activation` varchar(100) NOT NULL DEFAULT '',
  `params` text NOT NULL,
  PRIMARY KEY (`id`),
  KEY `usertype` (`usertype`),
  KEY `idx_name` (`name`),
  KEY `gid_block` (`gid`,`block`),
  KEY `username` (`username`),
  KEY `email` (`email`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1798 ;

[Psycho]

Well, I don't know what to tell you. I used the table structures above to exactly duplicate the tables you have. Then I populated them with some sample data as follows:

 

jos_users

id | name
------------
1   name1
2   name2
3   name3
4   name4

 

jos_vm_user_info

user_info_id | user_id | first_name
-----------------------------------
  1             1
  8             2
10             3

 

I then ran the exact script I provided above and the jos_vm_user_info table was changed as follows

user_info_id | user_id | first_name
-----------------------------------
  1             1        name1
  8             2        name2
10             3        name3

 

I also tried running AGuyWithAthing's query. Although I have seen that particular format before I kept getting a syntax error and couldn't find the proper format. But, I came up with the following that does do the same thing without a subquery and should be more efficient.

UPDATE jos_vm_user_info AS info
JOIN jos_users AS users ON info.user_id = users.id
SET info.first_name = users.name

 

Again, both the original query I provided and the one above work as expected against two tables set up exactly as you have them.

[rippergr]

The code seems ok but I don't know why it doesnt work in mt database. Is there any way so I can debug this to see what is going on in every line? Maybe with a php code?

 

[Psycho]

Is there any way so I can debug this to see what is going on in every line? Maybe with a php code?

 

What are you taking about "every line". There is only a single database query. Are you trying to run the query through PHP or are you running it via PHPMyAdmin (or whatever DB management tool you have)? If this is a one-time operation, just run it through PHPMyAdmin. If you need this to be run via a web-page, you should still test it via PHPMyAdmin first to see if it works.

 

As a test, you could run a select query to see that the JOIN is working correctly and what the "correct" results will be. The following SELECT query is exactly the same as the UPDATE query but it will show the current 'first_name' value in the info table and the current 'name' value in the users table. If you were to run the UPDATE query above, then the first column would have the same value as the second column.

SELECT info.first_name as first_name, users.name
FROM jos_vm_user_info AS info
JOIN jos_users AS users ON info.user_id = users.id

 

[rippergr]

Ok I got it. It was a stupid error in database .  Your code is working great.

Thanks for your help