Search query construction issues. Plz help!

PHP
1

I have 2 mysql tables containing data which relate to each other.
The main table contains a list of used cars and all their relevant data, ie make model price etc.

The second table contains a list of car makes and models along with a make id value and model id value for each one.

I have a script which generates an xml file using the data in the first table.
The problem is I need to take the relevant make and model id’s from the second table which correspond to each individual car and insert them into the xml file.

I can get the relevant make id to show in the xml file no problem as its a straight query as shown below:

The problems I have is the model id value. As an example the model value from the first table is SLK280. The model value from the 2nd table is SLK 280.

How can I set up a query that will see these two values as being the same and there fore output the relevant Model Id from the 2nd table?

I had tried the following but it doesn’t work:

$querymodel = “SELECT Model_Id FROM MyTable WHERE Make_Name=‘$row[Make]’ AND Model_Name LIKE ‘%$row[Model]%’”;

The values $row[Make] and $row[Model] are set previously on the page from an initial search query.

Any help would be much appreciated, thanks in advance.

2

try ‘%“.str_replace(” “,”%“,$row[‘Model’]).”%’ . Might be time to normalize your data though.

3

As Starlion says, you ideally need to normalize this data.

Google purchased an incredible tool for doing this kind of string matching at the end of last year.

Depending on how much data you have it might be worth dumping your data as csv and importing it into this tool.

Google Refine - watch the videos to find how they match 2 csv files.

4

Thanks for the quick reply on this, I’ve tried your code but it is not returning any values. I can’t change any of the data in the tables, which is a real pain! I think there must be an easy way to work this out, not sure if I explained this properly.

Here’s some sample data from the tables to better explain what I’m trying to do:

This is the structure and some content from the main table.

CREATE TABLE My_Stock (
Feed_Id text NOT NULL,
Vehicle_Id text NOT NULL,
Make text NOT NULL,
Model text NOT NULL,
FULLTEXT KEY Feed_Id (Feed_Id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO My_Stock VALUES (‘01234’, ‘17081393’, ‘MERCEDES-BENZ’, ‘SLK280’);
INSERT INTO My_Stock VALUES (‘01234’, ‘16911386’, ‘MERCEDES-BENZ’, ‘C180 KOMPRESSOR’);
INSERT INTO My_Stock VALUES (‘01234’, ‘16911354’, ‘MERCEDES-BENZ’, ‘CLC180 KOMPRESSOR’);

This is from the secondary table which I need to select the corresponding Model_Id values from.

CREATE TABLE My_Models (
Make_Id int(9) NOT NULL default ‘0’,
Model_Id varchar(9) NOT NULL default ‘0’,
Site_Id text NOT NULL,
Model_Name text NOT NULL,
Make_Name text NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO My_Models VALUES (17200, ‘17200-110’, ‘UK’, ‘SLK 280’, ‘Mercedes-Benz’);
INSERT INTO My_Models VALUES (17200, ‘17200-17’, ‘UK’, ‘C 180’, ‘Mercedes-Benz’);

Can anyone tell me how to set up a query which will pull the relevant Model_Id from the second table where the Model Name is contained in the Model field from the first table?

Any help much appreciated.

5

Assuming there’s not going to be an instance of Model “SLK 10 91” being different than “SLK 1091”…

SELECT Make_id,Model_Id FROM My_Models WHERE REPLACE(Model_Name," “,”“) = REPLACE($row[‘Model’],” “,”");

6

Obviously being a bit thick with this because I still can’t get this to work! I’m getting the following error when I’m using the code you said.

Parse error: syntax error, unexpected T_CONSTANT_ENCAPSED_STRING

Here’s exactly what I have, have I did it wrong?

$querymodel = “SELECT Make_Id,Model_Id FROM My_Models WHERE REPLACE(Model_Name,” “,”“) = REPLACE($row[‘Model’],” “,”“)”;

7

Follow the bouncing underline…
$querymodel = 'SELECT Make_Id,Model_Id FROM My_Models WHERE REPLACE(Model_Name," “,”“) = REPLACE('.$row[‘Model’].',” “,”")';

(EDIT: Decided Underline not a good idea when I have underscores in the query. Replaced with Red)

Having " around your string means that you need to escape any "s you want inside the string - otherwise PHP doesnt know where you wanted your string to start and end. An alternative is to use apostrophes to wrap the string, which disables escape characters, but allows you to use quotation marks without problem. Take a look at PHP Manual - Strings if you’re confused by this.

8

Getting there I think, I’m now getting: Unknown column error ‘B160’ in ‘where clause’

Any ideas?

9

yeah i had a feeling it was going to do that…
Try making it WHERE Model_Name = REPLACE(‘.$row[‘Model’].’," “,”")';

10

Still getting the same errors, sorry, any other ideas?

11

oh dur…
WHERE REPLACE(Model_Name," “,”“) = REPLACE("‘.$row[‘Model’].’",” “,”")';

have to wrap the string or it thinks it’s a column name.

12

Thanks so much for all the help with this! You’re a genius mate!!
That code works, but when I look at the xml file that its being used to generate it has blank spaces in some places where it should be putting in these values. I think its because it can’t find a match in the second table for some of the values. Would there be a way to modify the code I have to return a value of OTHER if the query doesn’t find a match in the second table, and therefore can’t display the relevant Model_Id value?

13

It may be easier to handle that in PHP rather than through SQL. What PHP code are you using for this?

14

The code I’m using to get the Model Id value is below.

$querymodel = ‘SELECT Make_Id,Model_Id FROM My_Models WHERE REPLACE(Model_Name," “,”“) = REPLACE(”’.$row[‘Model’].‘“,” “,”")’;
$resultmodel = mysql_query($querymodel) or die(mysql_error());
$row_model = mysql_fetch_array($resultmodel);

I was going to use an if statement so that of the Model Id couldn’t be retrieved form the table the value displayed would simply be OTHER. How would I code that? I’ve tried the code below and it doesn’t work.

if($row_model[‘Model_Id’]==‘’){$new_model_value = ‘OTHER’;}
elseif($row_model[‘Model_Id’]!=‘’){$new_model_value = $row_model[‘Model_Id’];}
$_xml .=“<model>” . $new_model_value ."</model>\r
";

What am I doing wrong with this? The above still shows the Model Id values in the generated xml that it finds but not the value of OTHER if it can’t find anything to match with.

15

$row_model = mysql_fetch_array($resultmodel);

will report an error if there were no results to fetch.

Try this in the short term:


if(mysql_num_rows($resultmodel) == 0) {
  $new_model_value = "Other";
} else {
 list($row_make,$new_model_value) = mysql_fetch_row($resultmodel);
}

If you’re not going to use the Make_Id, drop it from the query, and then remove $row_make, from the above.

If there are a lot of models you’re doing this for, you might want to look at a better way of querying this information.