dmorison
msg:4504894 | 5:28 pm on Oct 6, 2012 (gmt 0) |
Hi,
Wouldn't it be just
$sql = "SELECT ref FROM log WHERE ref REGEXP '^[0-9]'";
|
LinusIT
msg:4505320 | 7:24 am on Oct 8, 2012 (gmt 0) |
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
msg:4508000 | 7:59 pm on Oct 14, 2012 (gmt 0) |
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
msg:4508005 | 9:17 pm on Oct 14, 2012 (gmt 0) |
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
msg:4508085 | 7:43 am on Oct 15, 2012 (gmt 0) |
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
msg:4508305 | 10:41 pm on Oct 15, 2012 (gmt 0) |
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
msg:4511424 | 8:07 pm on Oct 23, 2012 (gmt 0) |
Thats great, I've got it working correctly now. preg_match hadn't even entered my head.
Thanks very much
|
|
