[SOLVED] problem with MySQL search

[pornflakes]

Hi guys,

 

I got this problem with my search: it can´t find any results when the content of the form(select) has more than one word.

i.e. if it´s filled with "nothing" the search results are fine but if it´s filled with "nothing here" it shows no results.

 

It gets the list of the action from the mysql table fine.

(the table has collums : id,code,action,date,insert)

<form method="post" action="search.php">
    Choose action:<br>
<select name="laction">
    <?php
$query2 = mysql_query("SELECT DISTINCT action FROM test ORDER BY id");
while($col2 = mysql_fetch_array($query2))
{
	echo "<option value=" . $col2['action'] .">" . $col2['action'] . "</option>";
}
     ?>
        </select>
    <input type="submit" value="Search" />
    </form>

 

then searches

 

<?php
$laction=@$_POST["laction"];

if (isset($laction)){

$result = mysql_query("SELECT * FROM test WHERE LOWER(action) = LOWER('$laction') ORDER BY id");	
}
?>

 

This is where it shows the result.

<?php 
echo "<table border='1'>
<tr>
<th>Code</th>
<th>Action</th>
<th>Date</th>
<th>Insert</th>
</tr>";

while($row = @mysql_fetch_array($result))
  {
  echo "<tr>";
  echo "<td>" . $row['code'] . "</td>";
  echo "<td>" . $row['action'] . "</td>";
  echo "<td>" . $row['date'] . "</td>";
  echo "<td>" . $row['insert'] . "</td>";
  echo "</tr>";
  }
echo "</table>"; 
?>

 

btw I´m still kinda new to this...

Thanks for your time

[MadTechie]

Could you post some samples of what you have tried and what's in the database, for example is "nothing here" in the database ?

 

if you have something like

their is nothing to be seen in here

in the database and you want to find entries that that "nothing" and "here"

then you could use LIKE

SELECT * FROM test WHERE action LIKE '%nothing%' AND action LIKE '%here%'  ORDER BY id

or for fulltext searches

SELECT * FROM test WHERE MATCH(action) AGAINST('nothing','here')ORDER BY id

[pornflakes]

ok, the table looks like this :

 

id code action                date               insert

1 655 nothing         12/08/2009 21:03         Luke

2 655 something       12/08/2009 21:03       dude1

3 700 nothing here      13/08/2009 19:59      dude1

 

the search from the form is fine for "nothing" and "something" is ok, but when I select "nothing here" in the form, the result of the search is empty

[MadTechie]

Strange, (moving to SQL section)

 

Any weird characters ?

 

what about trying

action  LIKE '%nothing%here%'

[pornflakes]

No weird characters, just text space and text.

That should work,

action  LIKE '%nothing%here%'

...but I would need it to work with the variable...

I just don´t understand how can it work perfectly fine when "nothing" or "something" is stored inside the variable, but when "nothing here" is stored there it doesn´t work...

[pornflakes]

Some additional info...

 

MySQL server version 5.3.0

I get no errors, it just doesn´t show any results.

 

The table looks like this:

CREATE TABLE `test` (
`id` int(15) NOT NULL AUTO_INCREMENT,
`code` int(15) NOT NULL,
`action` varchar(30) NOT NULL,
`date` varchar(20) NOT NULL,
`insert` varchar(25) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1

[MadTechie]

I couldn't see any reason for this not working so I tried it and  it works fine

SELECT * FROM `test` WHERE `action` = 'nothing here'

 

1 total, Query took 0.0009 sec)

3  700  nothing here  13/08/2009 19:59  dude1

 

 

Setup

--
-- Table structure for table `test`
--

CREATE TABLE `test` (
  `id` int(15) NOT NULL auto_increment,
  `code` int(15) NOT NULL,
  `action` varchar(30) NOT NULL,
  `date` varchar(20) NOT NULL,
  `insert` varchar(25) NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;


INSERT INTO `test` (`id`, `code`, `action`, `date`, `insert`) VALUES
(1, 655, 'nothing', '12/08/2009 21:03', 'Luke'),
(2, 655, 'something', '12/08/2009 21:03', 'dude1'),
(3, 700, 'nothing here', '13/08/2009 19:59', 'dude1');

[pornflakes]

Ya, exactly, but I posted it in PHP forum first because of this:

<form method="post" action="search.php">
    Choose action:<br>
   <select name="laction">
    <?php
   $query2 = mysql_query("SELECT DISTINCT action FROM test ORDER BY id");
   while($col2 = mysql_fetch_array($query2))
   {
      echo "<option value=" . $col2['action'] .">" . $col2['action'] . "</option>";
   }
     ?>
        </select>
    <input type="submit" value="Search" />
    </form>

 

The search in the mysql itself is fine, but when I use the variable for searching there is that problem. The problem is that I does not show any results when I use this html form to do the search... 

It loads the values from the table ok, does the search fine but only for the entries from the table, that have 1 word, for the "nothing here" value it does not find anything.

Am I explaining my point wrong here or something?..

[MadTechie]

Echo the query to check its correct and valid

[pornflakes]

Which query?

 

if I echo

echo $result;

I get

Resource id #7 

 

echo $query2;

I get

Resource id #5 

 

[pornflakes]

Finaly figured it out, I´ll just post it if someone makes the same mistake.

In the select form I just changed the option value to id of the action and the search turned out fine.

<form method="post" action="search.php">
    Choose action:<br>
   <select name="laction">
    <?php
   $query2 = mysql_query("SELECT DISTINCT id, action FROM test ORDER BY id");
   while($col2 = mysql_fetch_array($query2))
   {
      echo "<option value=" . $col2['id'] .">" . $col2['action'] . "</option>";
   }
     ?>
        </select>
    <input type="submit" value="Search" />
    </form>

 

Then the select

<?php
$laction=@$_POST["laction"];

if (isset($laction)){
   
   $result = mysql_query("SELECT * FROM test WHERE (id) =  ('$laction') ORDER BY id");   
}
?>