Welcome to WebmasterWorld Guest from 54.234.63.187

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

Select where begins with a number

     

LinusIT

8:01 am on Oct 6, 2012 (gmt 0)

5+ Year Member



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

5:28 pm on Oct 6, 2012 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



Hi,

Wouldn't it be just


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

LinusIT

7:24 am on Oct 8, 2012 (gmt 0)

5+ Year Member



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

7:59 pm on Oct 14, 2012 (gmt 0)

5+ Year Member



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

9:17 pm on Oct 14, 2012 (gmt 0)

WebmasterWorld Senior Member swa66 is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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

7:43 am on Oct 15, 2012 (gmt 0)

5+ Year Member



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

10:41 pm on Oct 15, 2012 (gmt 0)

WebmasterWorld Senior Member swa66 is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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

8:07 pm on Oct 23, 2012 (gmt 0)

5+ Year Member



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

Thanks very much
 

Featured Threads

Hot Threads This Week

Hot Threads This Month