Function Syntax Wrong - Mysql Error

[unemployment]

Have I created this function properly?  I am getting a mysql error.

 

function update_personal_info($username, $firstname, $lastname, $dateofbirth, $city, $state, $country, $personalweb, $credentials, $specialties, $email, $mail_status, $capitalavailable = null, $phonenumber)
{
$firstname			= mres(htmlentities(strip_tags($firstname)));
$lastname			= mres(htmlentities(strip_tags($lastname)));
$dateofbirth		= mres(htmlentities(strip_tags($dateofbirth)));
$city				= mres(htmlentities(strip_tags($city)));
$state				= (!empty($state)) ? (int)$state : null;
$country			= (int)$country;
$personalweb		= mres(htmlentities(strip_tags($personalweb)));
$credentials		= mres(htmlentities(strip_tags($credentials)));
$specialties		= mres(htmlentities(strip_tags($specialties)));
$email				= mres(htmlentities(strip_tags($email)));
$mail_status		= (int)$mail_status;
$phonenumber		= mres(htmlentities(strip_tags($phonenumber)));
$capitalavailable	= (int)$capitalavailable;

	$sql = "UPDATE `users` SET
				`firstname` 	= '{$firstname}',
				`lastname`		= '{$lastname}',
				`city` 			= '{$city}',
				`state` 		= '{$state}',
				`country` 		= '{$country}',
				`email`			= '{$email}',
				`phonenumber` 	= '{$phonenumber}',
				`personalweb` 	= '{$personalweb}',
				`credentials` 	= '{$credentials}',
				`specialties` 	= '{$specialties}',
				`mail_status` 	= '{$mail_status}'
			WHERE `username` 	= '${username}'";

    if ($capitalavailable != null)
{
	$uid = $GLOBALS['user_info']['uid'];

	$sql .= "UPDATE `investor_info` SET
				`capital_available`	= '{$capitalavailable}'
			WHERE `uid` = '${uid}'";
}

return mysql_query($sql) or die(mysql_error());
}

[fugix]

first thing that i notice is you are trying to concatenate 2 queries together, cannot do this

[unemployment]

first thing that i notice is you are trying to concatenate 2 queries together, cannot do this

 

Then how do I combined them?  Can I even do that?

[KevinM1]

What's stopping you from simply running two queries?

[KevinM1]

But, yes, if you want to update everything in one query, you can simply use dot notation:

 

"UPDATE table1, table2 SET table1.column = someValue, table2.column = someOtherValue"

 

http://dev.mysql.com/doc/refman/5.0/en/update.html

[unemployment]

What's stopping you from simply running two queries?

 

Is running two queries msot efficient?

[KevinM1]

What's stopping you from simply running two queries?

 

Is running two queries msot efficient?

 

No, it's less efficient, but unless you're pulling a lot of data, you probably don't need to worry about it too much.  And, again, I provided you the information you need to combine queries in my post above, anyway, in case you discover that two queries create too much of a bottleneck for your system.

[void]

Other than the query issues, code like that makes baby Jesus cry. I suggest discovering arrays.

[unemployment]

Other than the query issues, code like that makes baby Jesus cry. I suggest discovering arrays.

 

Are you suggesting I put my parameters in an array?  I love to learn so please explain.

[xyph]

Nightslyr - Updates that hit multiple tables without a WHERE clause? SCARY!!!

 

Just perform this in two queries. You COULD do it in one, but you'd have to be VERY careful with your logic. The slight overhead to do this is two queries is worth it, unless you need absolute efficiency.

[jcbones]

Other than the query issues, code like that makes baby Jesus cry. I suggest discovering arrays.

 

Are you suggesting I put my parameters in an array?  I love to learn so please explain.

 

Yes, that is what he is saying.

 

On to another note, you should code functions so they are usable in a variety of situations.  When you code a function so that it can only be used in one place of your code, it defeats the purpose of building a function.  You could (and should) just write it out in the script.  But, you can change this function (using arrays) so that you could run ANY update query through it, and make it work.  Since you are having to type out the values anyway, just put them in a associative array, and let the function do the rest of the work.

 

Example:

function update(string $table, array $data, array $where) {
	foreach($data as $key => $value) { //go through the $data array.
		$info[] = $key . ' = \'' . mysql_real_escape_string(strip_tags($value)) . '\''; //build the column = value for each of the array members.
	}
	foreach($where as $key => $value) { //now go through the where array.
		$find[] = $key . ' = \'' . mysql_real_escape_string($value) . '\'';  //building these columns like those above.
	}

$sql = "UPDATE $table SET " . implode(', ',$info) . ' WHERE ' . implode(' AND ',$find); //build our query, turning the new info array into a comma separated string, and our where array into a string separated by AND.
if(mysql_query($sql)) { //if the query executes.
	return mysql_affected_rows(); //return the affected rows.
}
return false;	//if it doesn't return false.
}

//set the fields and the values inside an array.
$arr['firstname'] = $firstname;
$arr['lastname'] = $lastname;
$arr['dateofbirth'] = $dateofbirth;
$arr['city'] = $city;
$arr['state'] = $state;
$arr['country'] = $country;
$arr['personalweb'] = $personalweb;
//etc.
//now set you where clause in an array.
$find['username'] = $username;
//now pass all this info to the function, with the table name;
update('users',$arr,$find);

if(!empty($capitalavailable)) {
$arr['capital_available'] = $capitalavailable;
$find['uid'] = $GLOBALS['user_info']['uid'];
//update the investor info table,
update('investor_info',$arr,$find);
}


//Now this function is NOT dependent on this specific use.  As we can now pass any table, and any columns to it.
$arr['make'] = 'Ford';
$arr['model'] = 'F250';
$arr['price'] = 49,999;
$find['city'] = 'Memphis';
$find['state'] = 'Tenn';
update('cars',$arr,$find);

[KevinM1]

Nightslyr - Updates that hit multiple tables without a WHERE clause? SCARY!!!

 

Didn't have time for a fleshed out example, so I highlighted the most important part.  A WHERE clause should be obvious to all but newbies.

[xyph]

$uid = $GLOBALS['user_info']['uid'];

 

I'm guessing a newbie. Not knocking you, just making sure it's known that your code snippet is incomplete and dangerous.

[unemployment]

great sutff guys.  You have all been very helpful.  I ultimately decided to do this with two queries.