On 02/01/2022 13:51, gordonisnz@xxxxxxxxx wrote:
Hi, sorry for the 2nd query today.
https://dev.mysql.com/doc/refman/8.0/en/regexp.html
I'm looking at the REGEXP screens and am a bit puzzled.
Basically all the examples shown are very similar to preg_match in PHP.
long bit of text, find short bit of text - if its included etc..
However, I cannot see an example of how this is effective / used in a
SELECT of a MYSQLI database/table. it's only rexgexp'ing against
straight strings..
ive got a current SQL - which uses JOINS.
$sql = "SELECT Users.Username, Entities.*, Teams.* FROM Teams
LEFT JOIN Users
ON Users.PlayerID = Teams.PlayerID
LEFT JOIN Entities
ON Entities.PlayerID = Teams.PlayerID
WHERE Teams.TeamID='$teamnumber';";
This works pretty well. except i'm wanting to add a regexp into it.
Basically a regexp where Teams.playerID does NOT begin with "*"
(shifted-8)
similar to:
if(! preg_match("/\*/",$team[playerID])
{ get all the records in the joined table }
I'll also copy the SQl in another table where playerID DOES match * (shift-8)
In both situations the PlayerID starts with * (or doesn't start with *)
1) get the team ID (primary start number)
2) get all PlayerID's with NO stars at the start of their player-Id
3) Do the joining and get the resulting data
Do the same 3 steps except I'm selecting players WITH a * at the start
of their player Id's - i'll select from a different table name.
I think a regular expression check inside the SQL would be overkill if
you only want to omit results where the playerID field begins with a
literal * character. Instead you can add another WHERE clause:
...
WHERE Teams.TeamID='$teamnumber'
AND Teams.PlayerID NOT LIKE '*%'
You will need a full text index on the columns you're performing the
LIKE on, otherwise performance is going to be pretty bad.
On another note, you should probably look at using something like PDO to
create a parameterised query, rather than building the entire query
string yourself and inserting values into it manually. Depending on
where that variables value is coming from, you could be opening yourself
up to SQL injection attacks.
--
Ashley Sheridan
https://www.ashleysheridan.co.uk
