homepage Welcome to WebmasterWorld Guest from 54.234.141.47
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

    
Select where begins with a number
LinusIT



 
Msg#: 4504777 posted 8:01 am on Oct 6, 2012 (gmt 0)

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

WebmasterWorld Senior Member 10+ Year Member



 
Msg#: 4504777 posted 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#: 4504777 posted 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#: 4504777 posted 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

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



 
Msg#: 4504777 posted 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#: 4504777 posted 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

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



 
Msg#: 4504777 posted 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#: 4504777 posted 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

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved