Comparing strings returned from a mySql query

Question

I am querying a large number of codes from my database, and need to have some validation before a user can input another code in to the database.

An example code would be this:

TD-BR-010212-xxxxxxxx

Where TD represents a promotion, BR represents a place, the numbers represent a date, and the rest are random.

My problem is that before the code is entered into the DB, I want to check to see if the date and place for that code already exists, as they should not be allwed to enter a code from the same place and date.

I assume it would be something within a loop as I already have:

$location_part_of_td = $code[2].$code[3];
$date_part_of_td = $code[4].$code[5].$code[6].$code[7].$code[8].$code[9];

$trade_day_result = mysql_query('SELECT * from wp_scloyalty WHERE promotion_type = trade-day') or die(mysql_error());  // Pulls all trade day codes from the database and checks the date part of the code. 

// the date part exists with the same area part, user cant redeem.
while($info = mysql_fetch_array( $trade_day_result )) 
{ 

     $code = $info["product"];

} 

But Im just not sure about the best way to check the strings..

Answer 1

You can use a MySQL LIKE clause to get entries in your DB that resemble your code.

Example:

$code_exists = mysql_query( 
      "SELECT 'a' FROM table_name WHERE column_name LIKE 'TD-BR-010212-%'"
);
if(mysql_num_rows($code_exists) > 0) {
    // The specified place/date is taken
} else {
    // No promotion at place BR on the specified date.
}

The '%' is used as a wildcard in SQL LIKE clauses.

Answer 2

You have two approach to solving this issue. Assuming you have access to alter the table.

Add a unique constraint to the table base off of the two columns.

Or Your approach by selecting all of the Location and Date, and see if it return any results. SQL: SELECT COUNT(*) as counter FROM table where column = 'TD-BR-010212-%' And check to see if counter return > 0;

Answer 3

I would use the LIKE statement in your SELECT and pull entries that start with the same promotion, place, and date. Unfortunately I don't know how your table looks so bear with me:

$promo_query = "SELECT * FROM wp_sclocalty WHERE column_name LIKE 'TD-BR-010212-%'";
$promo_result = mysql_query($promo_query);

if(mysql_num_rows($promo_result) == 0) {
    // the promo code has NOT been used
} else {
    // the promo code HAS been used
}

Answer 4

try this query

$part_code=substr($code, 0)

$records =mysql_query("select id from tableName where SUBSTRING(code,1,12)= $part_code");

if(mysql_num_rows($records) > 0)
{
 // Duplicate exit
}
else
{
 // insert code in DB
}

Answer 5

If you can, you'll get better performance and easier coding if you break apart the code into different fields when you save the data in each row. That way you can write queries that specifically check values for the components pieces of the code - you can even set rules in the database (like UNIQUE) to ensure that some parts are kept unique.

Specifically, I'd suggest:

create table your_table (
    [... your other columns ...]
    promotion char(2),
    place char(2),
    pr_date date,
    pr_ident varchar(50)
)

Your first row would be ([...], 'TD','BR','2012-01-02', 'xxxxxxxx'). And queries would not require unpacking the formatted string - you could say things like "where promotion = 'TD' and place in ('BR','XX') ...". Simple, eh?