Help in selecting from database where field has a -

[xray_griff]

I have a nice database supplied to me with a lot of items in it. I need to select from a column called PartNo anything that partially matches a form post

I am using DW, so the %%%s%% is drawn from the form post.
SELECT * FROM PartsTable WHERE PartNo LIKE '%%%s%%' ORDER BY PartNo ASC

This is where the fun starts the database contains a lot of part numbers that contain a -
every search I do returns the values below (I did search for 8029 in this case and I have <B> my target).

Short of removing every - is there anything I can do to stop thq SQL from selecting these results every time and only return them when they are part of a relevant search?

Cheers
xray_griff


AT100-105 BR2501-1 CEM-2808-1420 CEM-2814-1030 CEM-2820-1200 CEM-ESC-10 CEM-ESC-12BL CEM-MUF-120 CM1213-1 CP2504/5A-1 F1213-1 F1213A-1 FAST02-1 FAST187-10 FAST233-1 FAST234-1 FAST742-1 FOT-1719 FOT-1876 FOT-1910 FOT-1910G G150004-1 GST075-1 H87032-1 H87047-1 H87329-1 H87356-1 HB10801-1 HB10806-1 HB110-1 HB389-1 HB61165-1 HB61181-1 HB61182-1 HBC5014-1 HBC8001-1 HBC8005-1 HBC8006-1 HBC8007-1 HBC8009-1 HBC8010-1 HBC8011-1 HBC8018-1 HBC8020-1 HBC8024-1 HBC8027-1 HBC[b]8029[/b]-1 HBC8033-1 HBC8034-1 HBC8043-1 HBC8051-1 HBC8055-1 HBC8056-1 HBC8058-1 HBC8068-1 HBC8074-1 HBC8076-1 HBC8077-1 HBC8078-1 HBC8079-1 HBC8080-1 HBC8081-1 HBC8084-1 HBC8085-1 HBC8101-1 HBC8102-1 HBC8110-1 HBC8116-1 HBC8118-1 HBC8120-1 HBC8121-1 HBC8133-1 HBC8161-1 HBC8163-1 HS101-1 HS102-1 HW1203-1 K-1BCV K-1DKV K-1DKV/G K-1MCV K-1RXC K-1RXC/301 K-1RXC/302 K-1STV K-1TBV K-1TMA K-1TP K-1TPA K-1VST-TH K-1VSU-ST K1001-1 K1102-1 K1106-1 K1118-1 K1146-1 K1146-10 K1146-11 K1146-12 K1146-13 K1146-14 K1146-15 K1215-1 K1215-11 K1215-12 K1242-1 K1243-1 K1244-1 K1339-1B K1410-1 K14104-1 K14109-1 K1430-1B K1439-1 K1480-1 K1497-1 K1497-10 K1497-11 K1497-12 K8001-1 K8011-1 K83002-10 K83002-11 K83002-12 MM038-1 MRBC-1S PL6026-11 PL6027-11 PL6028-11 PL6204-1 PL9014-10 PL9014-11 PL9017-10 PL9017-11 PL9018-10 PL9018-11 PL9020-11 PL9021-10 R1213-1 RB01060-1 RB01100-1 RB01102-1 RB01150-13S RB01150-14 RB01162-13B RB01163-13B/CC RB01164-14B RB01272-1 RB01274-1 RB01291-10 RB01351-12P RB01351-13P RB01700-100A RB01700-110 RB01700-112 RB01700-113 RB01700-114 RB01700-123 RB01700-151 RB01700-152 RB01700-154/1 RB01700-171 RB01700-172 RB01700-193 RB01751-195 RB01751-196 RB01751-197 RB01900-120 RBE1003-E-1 RBE1004-WS7-1 RBE1351-1263P RBE1351-1286P RBE1351-132020P SP8443-1 TC134-1 TG2751-1 TG2752-1 TG2753-1 TM111034-1 TM116037-1 TM116038-1 TM116039-1 TM119212-1 VENF7620-1 XEV4030-1 XSW-102 YS2-1 YS4SS-1 YS53S-1T YS5S-1 YS5SSS-1 YS7-1 ZM672-1 ZR017-1 ZR018-1 ZS415S-1

[fenway]

I have no idea what '%%%s%%' represents -- having multiple wildcard characters in a row makes no sense. If you want to match '8029', you need to run the following query:

[code]SELECT * FROM PartsTable WHERE PartNo LIKE '%8029%' ORDER BY PartNo ASC[/code]

Of course this can come from a variable. And I don't see what the hyphen has to do with this at all. Please clarify.

[corbin]

Why cant

SELECT * FROM table where field = '$itemnum'; be used...? Of course that would all be changed to your stuff, but can mysql not handle hyphens in queries? Or what is the problem?

[xray_griff]

I have let DreamWeaver with PHAkt 3.2.0 do all the coding, so that that is why all the wild cards are there. You can see from the code below I just wanted to get on with the job of accessing the database.

[code]$maxRows_Recordset1 = 500;
$pageNum_Recordset1 = 0;
if (isset($_GET['pageNum_Recordset1'])) {
  $pageNum_Recordset1 = $_GET['pageNum_Recordset1'];
}
$startRow_Recordset1 = $pageNum_Recordset1 * $maxRows_Recordset1;
$colname__Recordset1 = '-1';
if (isset($_POST['PartNo'])) {
  $colname__Recordset1 = $_POST['PartNo'];
}
$query_Recordset1 = sprintf("SELECT * FROM PartsTable WHERE PartNo LIKE '%%%s%%' ORDER BY PartNo ASC", $colname__Recordset1);
$Recordset1 = $connPodium->SelectLimit($query_Recordset1, $maxRows_Recordset1, $startRow_Recordset1) or die($connPodium->ErrorMsg());
if (isset($_GET['totalRows_Recordset1'])) {
  $totalRows_Recordset1 = $_GET['totalRows_Recordset1'];
} else {
  $all_Recordset1 = $connPodium->SelectLimit($query_Recordset1) or die($connPodium->ErrorMsg());
  $totalRows_Recordset1 = $all_Recordset1->RecordCount();
}
$totalPages_Recordset1 = (int)(($totalRows_Recordset1-1)/$maxRows_Recordset1);[/code]


SELECT * FROM table where field = '$itemnum' would be great if the suppliers would not mess with primary keys. There are 4 suppliers with 4 different database tables of parts. I have managed to get them in to one table (36,000 entries) the problem is 2 of the suppliers have added there own prefix and suffix in the database to the part numbers. Most of these parts are for radio control cars. If you read the instructions for example a nitro engine may be part number 8029 in the instruction manual and that is what you would want to order. The supplier then adds something like a H so they know which manufacturer it is from. The instruction manual may not have a part number for the engines piston and other parts so the supplier will create suffix of -29 for the piston or -1 for the engine.
So in our database we have H8029-29 for the piston when all the client has as part number of 8029. Therefore I need something a little more to find the all the possible parts.

Thank you for your help and I will have a go at reviewing the wild cards.

[fenway]

I suggest you try and run a "normal" looking query from the command-line or a proper MySQL client, and then convert it to DW format.