[RESOLVED] Searching within a field

[kostis12345]

I have a column in my table called Buildings.In each row,in that column, there is a list of buildings a user has created separated by comma.
I'm stuck finding a way to search through it to find the name of a specific building and I hope that someone here can point me to the correct direction (or function in this case)

Thanks in advance

[bradgrafelman]

Well, if it's a comma-separated list, you could check if FIND_IN_SET() is greater than 0, otherwise you could just do a LIKE comparison.

The better solution, however, would be to normalize your database and get rid of the comma-separated column.

[kostis12345]

Thanks a lot
If I normalize the DB,how would the words be sepatated?With spaces?And how would I search in that column?
Thanks in advance

[coldwerturkey]

kostis12345;10911710 wrote:

Thanks a lot
If I normalize the DB,how would the words be sepatated?With spaces?And how would I search in that column?
Thanks in advance

im pretty sure it would be a new table that would be associated via 'buildings' primary key, ie; instead of like three values in one cell with the two comas, the values would be entered in three separate rows on the new table with the same associating value.

[kostis12345]

Wouldn't this make an enormous table if I have a big user list and each user has 10 buildings ?
Is there a way to enter the values of the field in a string and then search within the string ?

Thanks in advance

[laserlight]

kostis12345 wrote:

Wouldn't this make an enormous table if I have a big user list and each user has 10 buildings ?

Yes, the building table would then be relatively large, but luckily (or should I say by design?) relational database management systems tend to cater to having tables with many, many rows.

kostis12345 wrote:

Is there a way to enter the values of the field in a string and then search within the string ?

Probably, but unluckily performance might not be as good since you say, cannot make use of any column index.

[kostis12345]

Ok,thanks a lot,I'm going to try it.
I'm not marking it as solved in case I come across any problems.

[Weedpacket]

kostis12345 wrote:

Wouldn't this make an enormous table if I have a big user list and each user has 10 buildings ?

laserlight wrote:

Yes, the building table would then be relatively large

But not that large: the joining table would probably have only two or three numbers per record, compared with the amount of information being stored in each record of the user or building table. So while the joining table would have ten times as many records as the user table, the user table would probably still be physically larger.

And if you've got a good DBMS that has no trouble with a user table that has a million records, it's unlikely to have a problem with a join table that has ten million records.

[kostis12345]

I created a new table that has two columns,in the first on there are the name of the buildings and in the second the username of the user who has created them.

I want to echo all of the buildings a user has.I'm using the following code but it only echos the first "name" that matches the criteria.

<?php

/**
 * @author Constantine Loukas
 * @copyright 2009
 */
 include ('config.php');
session_start();
$checkuser = "SELECT name FROM Users_Buildings WHERE username = '" .$_SESSION['TheNewBattles'] . "'";
$query = mysql_query($checkuser);
$userbuildings = mysql_fetch_array($query);
echo $userbuildings[0]."<br/>";
echo $userbuildings[1];



?>

I think I have to loop it somehow(although I could be completely wrong) but I can't figure it out.

Thanks in advance

---

I managed to make a loop but it echos the same results.

my new code is this

<?php

/**
 * @author Constantine Loukas
 * @copyright 2009
 */
 include ('config.php');
session_start();
$checkuser = "SELECT name FROM Users_Buildings WHERE username = '" .$_SESSION['TheNewBattles'] . "'";
$query = mysql_query($checkuser);
$userbuildingrows = mysql_num_rows($query);

$counter = 1;
$userbuildings = mysql_fetch_array($query);
while ($counter <= $userbuildingrows)
{


echo $userbuildings[0]."<br/>";
$counter = $counter + 1;
}




?>

[kostis12345]

Ok,I found the way.
Thanks to everybody who helped.

Here's the code I'm currently using

<?php

/**
 * @author Constantine Loukas
 * @copyright 2009
 */
 include ('config.php');
session_start();
$checkuser = "SELECT name FROM Users_Buildings WHERE username = '" .$_SESSION['TheNewBattles'] . "'";
$query = mysql_query($checkuser);
$userbuildingrows = mysql_num_rows($query);

$counter = 1;
$start = 1;

while ($counter <= $userbuildingrows)
{
$checkuser2 = "SELECT name FROM Users_Buildings WHERE username = '" .$_SESSION['TheNewBattles'] . "'LIMIT $start,$userbuildingrows";
$query2 = mysql_query($checkuser);
$userbuildings = mysql_fetch_array($query);
echo $userbuildings[0]."<br/>";
$counter = $counter + 1;
$start = $start + 1;
}




?>

If anybody has any suggestions or improvements please post.

[laserlight]

kostis12345 wrote:

If anybody has any suggestions or improvements please post.

Yes, it is good to see that you came to a working solution, but unfortunately your solution leaves much to be desired. It is usually wrong to run SQL statements in a loop.

Here is a better solution that retrieves the building names for all the users sorted according to the username. Taking advantage of the fact that the building names are now lumped together with the corresponding username so a different username from the previous means a new set of building names, one pass of the result set is enough to print the usernames and associated building names:

$sql = sprintf("SELECT username, name FROM Users_Buildings
    WHERE username='%s' ORDER BY username", $_SESSION['TheNewBattles']);
$result = mysql_query($sql);
$current_username = '';
while ($row = mysql_fetch_assoc($result))
{
    if ($row['username'] != $current_username)
    {
        // End the list for the previous user.
        if (!empty($current_username))
        {
            echo '</ul>';
        }

    $current_username = $row['username'];
    echo '<p>' . htmlspecialchars($current_username) . '</p><ul>';
}
// Print the current building name.
echo '<li>' . htmlspecialchars($row['name']) . '</li>';
}
// If necessary, end the list for the last user.
if (!empty($current_username))
{
    echo '</ul>';
}

[kostis12345]

Thanks a lot for answering.
Could you please explain the code you posted because I can only understand the basic concept?
Thanks in advance.

[laserlight]

I explained it in the second paragraph, and also in the comments. Which part would you like me to elaborate on?

[kostis12345]

First of all thanks for helping.
Now,to the point.I can't understand the "%s" you have in the first query and I also need some help with this part
while ($row = mysql_fetch_assoc($result))

Thanks in Advance

[coldwerturkey]

kostis12345;10911902 wrote:

First of all thanks for helping.
Now,to the point.I can't understand the "%s" you have in the first query

read [man]sprintf[/man], just by looking at the line, it's pretty self explanatory.

kostis12345;10911902 wrote:

I also need some help with this part
while ($row = mysql_fetch_assoc($result))

read [man]while[/man] and [man]mysql_fetch_assoc[/man]

[kostis12345]

I forgot to add that I have already read the php manual entries about the stuff I'm asking help with(actually it was the first thing I did) and I still can't understand them.

[laserlight]

kostis12345 wrote:

Now,to the point.I can't understand the "%s" you have in the first query

You need to ensure that the input is properly escaped before using it in the SQL statement. One way of doing this is to use sprintf() to help you format the text of the SQL statement, in conjunction with the correct escaping function, which in this case is mysql_real_escape_string() (which I notice I forgot to put in). The %s format specifier itself just specifies that the result of mysql_real_escape_string($SESSION['TheNewBattles']) is formatted as a string in the position where the %s is in the text:

$sql = sprintf("SELECT username, name FROM Users_Buildings
    WHERE username='%s' ORDER BY username",
    mysql_real_escape_string($_SESSION['TheNewBattles']));

kostis12345 wrote:

I also need some help with this part
while ($row = mysql_fetch_assoc($result))

Recall that mysql_fetch_assoc() returns false when there are no more rows to retrieve from the result set. Therefore, I am able to assign the return value of mysql_fetch_assoc() to $row, and at the same time use it to control the loop.

[kostis12345]

Ok,thanks a lot for explaining.