2 Replies - 507 Views - Last Post: 19 March 2010 - 10:12 PM Rate Topic: -----

#1 FrozenSnake  Icon User is offline

  • En man från Sverige!

Reputation: 122
  • View blog
  • Posts: 997
  • Joined: 30-July 08

check flags in MySQL

Posted 19 March 2010 - 06:03 PM

I am working on a function that will check the column "flags" for specified flags.
This is what I have so far.
<?php
function check_access($x)
{
	global $db;
	$db->query("SELECT id, flags FROM users WHERE flags LIKE '%$x%'");
	if(mysql_num_rows($db->mysql_result) == 0)
		die("You do not have access!");
}
?>


But this code accept any number that matches $x in some way. Like this check_access("1:1"); if I have the flags "21:1, 32:14, [...]" in my column for one user and he try to access 1:1 he will be able to because he has 21:1. How can I do so only 1:1 is checked and not *1:1*? The column saves as "text", should I used explode or is something else better?

Is This A Good Question/Topic? 0
  • +

Replies To: check flags in MySQL

#2 Martyr2  Icon User is offline

  • Programming Theoretician
  • member icon

Reputation: 4334
  • View blog
  • Posts: 12,128
  • Joined: 18-April 07

Re: check flags in MySQL

Posted 19 March 2010 - 09:40 PM

For something like this I would pull out the whole flag string from the database and use a regular expression on it through PHP. That or explode it etc in PHP. Normally you wouldn't store multiple values in a column like this if you need to match a single flag. This goes against one of the principle ideas behind database normalization where each column has "atomic" values. That is, each column of a row should have a single value, not multiple values glued together.

So rather than having you redesign part of your database (and probably force you to change other code that is already working) I would suggest you pull out the string and work on it using PHP functions/regular expressions.

Edit: You could also throw a space into your LIKE clause too if you can guarantee that there is a space following commas between values.

:)

This post has been edited by Martyr2: 19 March 2010 - 09:41 PM

Was This Post Helpful? 1
  • +
  • -

#3 FrozenSnake  Icon User is offline

  • En man från Sverige!

Reputation: 122
  • View blog
  • Posts: 997
  • Joined: 30-July 08

Re: check flags in MySQL

Posted 19 March 2010 - 10:12 PM

I ended up with this solution
function check_access($x)
{
	global $db;
	$flags = explode(',', $x);
	$db->query("SELECT id, flags FROM users WHERE flags LIKE '%$flags[0]%'");
	if(mysql_num_rows($db->mysql_result) == 0)
		die("You do not have access!");
}

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1