Select where begins with a number

[LinusIT]

Hi

I'm trying to check for duplicate values within my database but am only interested in duplicates that begin with a number. I'm using REGEXP but must be doing something wrong.

$sql = "SELECT ref FROM log WHERE ref='$ref' REGEXP '^[0-9]'";

There is more code to go after this but I think the problem lies within the sql.

[dmorison]

Hi,

Wouldn't it be just

$sql = "SELECT ref FROM log WHERE ref REGEXP '^[0-9]'";

[LinusIT]

I'm trying to check if a reference has already been using inserted into the database. The reason for using REGEXP is I'm only interested in checking against records that start with a number.

I'll put some more of the code below so hopefully this will help.

$sql = "SELECT FROM log WHERE ref='$ref' REGEXP '^[0-9]'";
$result = mysql_query($sql) or die(mysql_error());
$num_rows = mysql_num_rows($result);

if ($num_rows == 0) {
..INSERT INTO DATABASE
} else {
..ALREADY EXISTS SO WARN THE USER
}

[LinusIT]

I'm still struggling with this but have had another idea, how about I select all the fields that begin with a number in one sql and then in another check to see if the reference I'm lookinf for exists.

First SQl:

$sql = "SELECT ref FROM log WHERE ref REGEXP '^[0-9]'";
$result = mysql_query($sql) or die(mysql_error());

I don't know how to write the second sql, it would be something like "SELECT * WHERE $ref IN $result" at a guess. Can anyone help with the second sql please?

Thanks

[swa66]

To get it right: you want to see if there are lines in your table where in a given column there's a value that
a. starts with a number
b. equals a reference

Correct ?

Then why not turn it around: check in your php if the reference starts with a number (leaving SQL alone), and if it does, check in SQL for a line with the value of the reference.

BTW: you can use SELECTs in other SELECTs, but before you go there, try to explain the problem you're trying to solve first.

[LinusIT]

Thats correct. What I'm working on is a cash register log where by when tickets are paid out the user enters the ticket number (reference) and amount on to the log. To prevent people paying out twice I want it to warn if the reference already exists. However I am only interested in references that begin with a number, which means they can enter a reference of say "Expenses" and it not come up with a warning.

Here's the basic form

<form action="#" id="insert_form" method="post" autocomplete="off">
<input type="text" name="ref" />
<input type="text" name="in" />
<input type="text" name="out" />
<input type="submit" value="Submit" />
</form>

Example of how I'd like it to work

1st = Ref: 1234, In: 0.01 - Doesn't exists so no warning
2nd = Ref: 1234, In: 0.01 - Exists so give a warning
3rd = Ref: Expenses, Out 123 - Starts with a letter so just insert

Does this explain the situation a little better?

[swa66]

Sure, as said above, mostly in pseudo code

// process input from the form, sanitise everything
// I'm assuming the value filled in in the ref field goes into $ref in here.

// test if the "ref" from your post starts with a digit
if ( preg_match ('/^[0-9]/',$ref) ) {
 // starts with a digit, lets' see if it can be found already in the database
 // SELECT ref FROM log WHERE ref='$ref' 
 //perform query and warn user as needed
 ...
} else {
 // record log
 ...
}

[LinusIT]

Thats great, I've got it working correctly now. preg_match hadn't even entered my head.

Thanks very much