updating mysql db using php

siobhan

Hi,

This was part of my previous thread but I'm posting it as a separate thread because I'm hoping somone will be able to help with the update side (am in a bit of a rush)... should be straightforward, I'm just not sure the best way to do it?

So, I've got the php code sorted for search and replacing a string but just have a few questions about the database side.

I need to edit each of the records in a handsets column (code below) and then repopulate each record with the appropriate edited handset list.

1) How do I put the handsets column in the $string below instead of typing in an actual string?

2) How do I get the edited string back into the database? I'm not sure what the best way to write the update statement would be??

3) How do I loop this through each of the 400 records making sure that the edited data matches the appropriate record

<?php
$string = "Nokia 3100 (49532 bytes)Nokia 3100b (3723 bytes)Nokia 3108 (12004 bytes) ";
$pattern = "/ (.+?)/";
$replacement = ",";
echo preg_replace($pattern, $replacement, $string);
?>

Thanks!
Siobhan

chyan

how did you store your handset type in your database?
if you store it one type of handset per row
you need to change your code like this:

<?php
$string = "Nokia 3100 (49532 bytes)";
$pattern = "/ (.+?)/";
$replacement = "";
echo preg_replace($pattern, $replacement, $string);
?>

but you need to get $string from you database record
so the first thing you should do is make query to your database to acquire your handset
type, like this example:

$query="select handset_type from handset order by ASC";
if(!$result=mysql_query($query,$conn)){
echo mysql_error();
exit;
}
while ($row=mysql_fetch_array($result)){ // start looping

$handset=$row[handset_type]; // this get your handset type and assign it to $handset variable


// then using your code to preg_replace the handset name

$string = $handset; 
$pattern = "/ \(.+?\)/"; 
$replacement = ""; 
$newhandset=preg_replace($pattern, $replacement, $string); 


// then reinsert your handset name to database after updating their name


$updating="update handset set handset_type='$newhandset' where
handset_type='$handset'";

if (!mysql_query($updating,$conn))
{
echo mysql_error();
exit;
}


} //end of looping

Roger_Ramjet

What you really need to do is to get your database structure right. A comma seperated list of handsets in one field is really a bad idea. Basic normalisation would give you

table games
game_id
game
etc

table handsets
set_id
name
etc

game_set_matrix
game_id
set_id

This is the standard relational DB solution to your type of application. You do not "just have to live with it for a while". All you are doing is storing up problems for the future. Assuming you have fixed your replace problem, you now have a comma delimited list in one field. Dump it out as a text file and read it back in to a new table as a CSV file. If you define the name field as unique then all repetitions will bounce off. Then run a query using LIKE to create the matrix, ie if an entry in the handsets table matches part of the handsets list in the games table, create an entry in the matrix.

siobhan

Thanks Chyan. Everything works but I can't seem to get the database to update when I run it through a web page. I've posted this as a new update question, since I can't get it to update any tables. Could this have something to do with IE6 and SP2 settings? It shouldn't should it?

siobhan

I agree completely that I need to fix the handset list! It's just that while I am fixing that and rewriting the php for the pages, I also need to have a workable fix for the interim.

Originally posted by Roger Ramjet
If you define the name field as unique then all repetitions will bounce off. Then run a query using LIKE to create the matrix, ie if an entry in the handsets table matches part of the handsets list in the games table, create an entry in the matrix.

I already have a handset table in the database which lists all handsets:

CREATE TABLE handsetlist (
handsetlistID int(11) unsigned NOT NULL auto_increment,
handset text NOT NULL,
brand text NOT NULL,
PRIMARY KEY (handsetlistID)
) TYPE=MyISAM AUTO_INCREMENT=75 ;

So what I basically need then is to create the game handset matrix. I am a bit unsure about how to do the matching bit so that I can create an entry in the matrix. Do you have a suggestion for how to best write this code?

Thanks ever so much
Siobhan

Roger_Ramjet

Lots of ways to crack this egg, in code or in sql. Also depends on what tools you have available. My first choice, since it is what I use everyday, would be Access/VBA and then load mysql with the results. In Access one would use the 'LIKE' operator to find out if an individual handset is in the handset list and append an entry in the matrix.

Assuming you are restricted to mysql/php then there are 2 mysql string functions that will do the job:

FIND_IN_SET(str,strlist)
Returns a value 1 to N if the string str is in the string list strlist consisting of N substrings. A string list is a string composed of substrings separated by ,' characters. If the first argument is a constant string and the second is a column of type SET, the FIND_IN_SET() function is optimized to use bit arithmetic. Returns 0 if str is not in strlist or if strlist is the empty string. Returns NULL if either argument is NULL. This function will not work properly if the first argument contains a comma (,') character.

mysql> SELECT FIND_IN_SET('b','a,b,c,d');
-> 2

or:

LOCATE(substr,str)
LOCATE(substr,str,pos)
The first syntax returns the position of the first occurrence of substring substr in string str. The second syntax returns the position of the first occurrence of substring substr in string str, starting at position pos. Returns 0 if substr is not in str.

mysql> SELECT LOCATE('bar', 'foobarbar');
-> 4
mysql> SELECT LOCATE('xbar', 'foobar');
-> 0
mysql> SELECT LOCATE('bar', 'foobarbar',5);
-> 7

This function is multi-byte safe. In MySQL 3.23, this function is case sensitive. For 4.0 on, it is case sensitive only if either argument is a binary string.

As you can see, a 0 means no match. So your insert into matrix query would be:

INSERT INTO matrix(game_id, set_id)
SELECT games.game_id, handsets.set_id
FROM games, handsets WHERE locate( handsets.name, games.handsets) >0

Each handset should now be matched with all games that have it listed. I have not checked the syntax so I'll be a bit chuffed if it runs first time, but I'm sure you get the idea. (I'm also guessing at the actual field names)

EDIT---------

On reflection, you should probably use the find_in_set on your comma delimited list as there may be things like a Nokia100 and a Nokia1000, which would produce false matches for the Nokia100 with my example.

INSERT INTO matrix(game_id, set_id)
SELECT games.game_id, handsets.set_id
FROM games, handsets WHERE find_in_set( handsets.name, games.handsets) >0