Search query only giving one result?

[genieuk]

Hello,

I was going to post the full script but don't think it will ne needed.

Basically i have this query:

// query db 
$query = mysql_query("SELECT * FROM `userinformation` WHERE `username` LIKE '%$search%' OR `email` LIKE '%$search%' OR
				`id` LIKE '%$search%' OR `first_name` LIKE '%$search%' OR `last_name` LIKE '%$search%' ORDER BY id ASC") or die(mysql_error());

Now when i do a search for example ja in phpmyadmin it gives me 4 results as 4 people have a username that begins with ja yet my query on my search form above only brings back one result.

The query in phpmyadmin goes like:

SELECT *
FROM `userinformation`
WHERE `username` LIKE '%ja%'

I have tried numerous ways with no luck. apart from the additional OR the query is the same yet it only gives on result. I tried removing the or(s) althou that suppose to obviously give back more results based on the LIKE in the query.

Does anyone know what could be causing it to only give one result?

Any help much appreciated as always.

Thanks.

[pritaeas]

Could it be that the query is returning 4 rows, yet your code isn't displaying them all ?

[genieuk]

Hi,

Thanks for the reply. I not had problems with queries etc on any other part of my admin i have built. Here is my full code.

Thanks.

<?php 
	include ("".$_SERVER['DOCUMENT_ROOT']."/admin/global.php"); // include global file
	require_once("".$_SERVER['DOCUMENT_ROOT']."/admin/session.php"); // include session file
	admin_error(); // check if user is logged in
	$title = " Member Search";
	include ("".$_SERVER['DOCUMENT_ROOT']."/admin/top.inc"); // include top.inc
?>

<?php
	
	if (isset($_POST['submit']))
	{	
		// Collect data from input box
		$search = htmlspecialchars(strip_tags(stripslashes(mysql_real_escape_string($_POST['search']))));	
		
		// Check / Validate data
		if(empty($search))
		{
			$search_empty .= '<p><span class="error">In order to perform a search you need to enter a search query.</span></p><br />';	
			$err++;
		}
		
		// If validation is ok continue
		if ($err == 0) 
		{
			// db connect
			sql_con();
			
			// query db 
			$query = mysql_query("SELECT * FROM `userinformation` WHERE `username` LIKE '%$search%' OR `email` LIKE '%$search%' OR
								 `id` LIKE '%$search%' OR `first_name` LIKE '%$search%' OR `last_name` LIKE '%$search%' ORDER BY id ASC") or die(mysql_error());
				
				if (mysql_num_rows($query) > 0)
				{
					?>
                    
                    <table width="100%" cellspacing="5" cellpadding="4">
  						<tr>
                            <th nowrap="nowrap" class="tablebg">User Details</th>
                            <th nowrap="nowrap" class="tablebg">Options</th>  
  						</tr>
                        
                <?php
				while($row = mysql_fetch_array($query))
				{
					$results_display_1 = '<p>You searched for <b>' . $search . '</b> and below are your results. <br /><br />';
					$results_display_2 = 
					'<tr>
					<th nowrap="nowrap" align="left" style="background-color:#F1F1F1; font-size:10px; font-weight:normal; line-height:15px;">
					<b>ID:</b> ' . $row['id'] . '<br />
                    <b>Status:</b> ' . $row['status'] . '<br />
					<b>Username:</b> ' . $row['username'] . '<br />
					<b>Full Name:</b> ' . $row['first_name'] . ' ' . $row['last_name'] . '<br />
					<b>Email Adddress:</b> ' . $row['email'] . ' &nbsp;<a href="email.php?emailuser='.$row['email'].'&username='.$row['username'].'">[email user]</a><br />
					<b>Registered on:</b> ' . $row['date_time'] . '<br />
					<b>Registration IP:</b> ' . $row['ip'] . '<br />
					<b>Last Visited:</b> ' . date('l dS F Y, g:i:s A', $row['last_visited']) . '<br />
					</th>
					<th nowrap="nowrap" style="background-color:#F1F1F1; font-size:10px;">
					<a href="memberview.php?view=' . $row['id'] . '">[ View ]</a> &nbsp; | &nbsp;
					<a href="memberview.php?view=' . $row['id'] . '">[ Edit ]</a> &nbsp; | &nbsp;
					<a href="email.php?emailuser=' . $row['email'] . '&username='.$row['username'].'">[ Email user ]
					</th>
					</tr>
					</table>';
					
				}
				}
				else 
				{
					$no_results .= '<p>Sorry your search <b><i>'.$search.'</i></b> returned zero results.</p>';
				}
		}
	}

?>

<h2>Search for Members</h2>

<form method="post" action="<?php $_SERVER['PHP_SELF']; ?>" style="width:95%;border:1px solid #F6F6F6;">

	<?php echo $search_empty; ?>
	
	<label for="search">Search for:</label>
    <input type="text" name="search" id="search" />
    <input type="submit" name="submit" value="Search"  />
    
</form>

<?php 
	echo '<br />';
	echo $results_display_1;
	echo $results_display_2;
	echo $no_results;
?>
<br /><br /><br /><br />
<?php
	include ("".$_SERVER['DOCUMENT_ROOT']."/admin/footer.inc"); // include footer.inc file
?>

[Atli]

Have you tried printing the actual query your code is executing, just to see if it is like you expect it to be?

$sql = "SELECT * FROM `userinformation` WHERE ...";
echo "<pre>$query</pre>";
$query = mysql_query($sql);

Also, I would question whether you should use htmlspecialchars on the value of $search before you put it into the query. You may be converting plain-text characters that would give you a match into a HTML entity that doesn't.

Unless all your data has also been run through htmlspecialchars before being inserted into the database?.. Which is something you should never do. (The database should be neutral; not specific to a single front-end.)

[genieuk]

Hi Alti,

Thanks for the reply. I opened a new document and i get no results. I adjusted the code you gave obviously where needed and get nothing.

Also what do you mean by the htmlspecialchars part?.

I have removed it as your rite, silly me for not thinking. But i add htmlspecialchars to all form elements that i know won't contain the predefined characters as set by php, most times i use regex as i prefer it for validation.

For example usernames cannot contain the below which htmlspecial chars converts due to my regex pattern on usernames hence why i added it as i know none of those characters below will be in the username row in database, althou i did add more to my original query so genuinely forgot to remove it.

& (ampersand) becomes &
" (double quote) becomes "
' (single quote) becomes '
< (less than) becomes &lt;
> (greater than) becomes &gt;

Not sure why i am not getting any results.

First i done it this way in blank page with no results:

<?php
sql_con()or die(mysql_error()); // db connect
$sql = mysql_query("SELECT * FROM `userinformation` WHERE `username` LIKE '%ja%'") or die(mysql_error());
while ($row = mysql_fetch_array($sql));
{
echo "<pre>".$row['username']."</pre>";
}
?>

Then i tried this way:

<?php
      sql_con() or die(mysql_error()); // db connect
      $sql = "SELECT * FROM `userinformation` WHERE `username` LIKE '%ja%' ";
      $query = mysql_query($sql);
      echo "<pre>$query</pre>";
?>

Again no results. If you look you will see i added ja as the search as i know there are 4 usernames that begin with ja. Also when i do the same search in phpmyadmin using %LIKE% command i get the 4 results but not via php script.

Yet the exact same query in phpmydamin below gives back 4 results. It's confusing to say the least. :confused:

SELECT *
FROM `userinformation`
WHERE `username` LIKE '%ja%'

Thanks

[almostbob]

while($row = mysql_fetch_array($query)) { /*php */ }

Not sure but is there usually a $row++ at the ned of this section to get the next row

[szkoda]

I've managed to get the drop down list to populate (a misplaced ';') but the textboxes still arnt being populated. Do you know what could be wrong with the mySQL?

[genieuk]

I've managed to get the drop down list to populate (a misplaced ';') but the textboxes still arnt being populated. Do you know what could be wrong with the mySQL?

Have you posted in wrong thread as this is the wrong one you posted in. If you have a problem make a topic for yourself unless you replied to wrong one.

while($row = mysql_fetch_array($query)) { /*php */ }

Not sure but is there usually a $row++ at the ned of this section to get the next row

Hi,

Not needed as i got * in query to get everyything from row that have username that is Like %ja% and mysql_fetch_array will return the entire row(s) and store in an array if it matches the search term ja which for some reason it isn't althou in phpmyadmin same query does give 4 results. Plus i used blank page with and without a simple text box to test and nothing.

Thanks.

[szkoda]

Sorry have somehow managed to post in the wrong thread. Ignore my post

[Atli]

First i done it this way in blank page with no results:

<?php
sql_con()or die(mysql_error()); // db connect
$sql = mysql_query("SELECT * FROM `userinformation` WHERE `username` LIKE '%ja%'") or die(mysql_error());
while ($row = mysql_fetch_array($sql));
{
echo "<pre>".$row['username']."</pre>";
}
?>

Do you have error reporting turned on?
If not there may be a suppressed error message the would explain this.

And don't you have to include the sql_con function into that code before you can use it? Or is that a part of some global include?

Try:

<?php
ini_set('display_errors', true);
error_reporting(E_ALL + E_NOTICE);

sql_con()or die(mysql_error()); // db connect
$sql = mysql_query("SELECT * FROM `userinformation` WHERE `username` LIKE '%ja%'") or die(mysql_error());
while ($row = mysql_fetch_array($sql));
{
echo "<pre>".$row['username']."</pre>";
}
?>

Except for that, this code looks perfectly fine, and should produce the results you want.

My first guess would be that there is something wrong with the database connection itself. Perhaps you are connecting to the wrong database, or there is some error in the connection that is being suppressed.
That would probably be in your sql_con() function. If you post that here, maybe we could spot the problem in there.

My second guess would be that there is some character encoding error. You may want to check which encoding is used by our databases and make sure that your code files are encoded the same way.

Also what do you mean by the htmlspecialchars part?

The htmlspecialchars function is often mistakenly used to validate/sanitize normal user input data; while in fact, it is meant to be used when placing untrusted text (including user input) into HTML output.

The function does in no way validate or sanitize normal user input. It only makes sure there are no HTML tags in the given string, replacing them with their respective HTML entities, so that the string can be safely put into HTML output without messing up the HTML page it is being injected into.

You should not use the function on data that is meant to go into a database, or be used in a SQL query; HTML entities serve no purpose in SQL. They are, in fact, little more than junk data.
(Unless, of course, you are specifically looking for HTML entities in a plain-text string... but even then the HTML entity itself is used as a string of plain-text characters, not a single entity)

Data should be stored in it's original form, and in plain text user input the & character is supposed to be & , not & and < character is supposed to be < , not < .

See what I mean?

[Atli]

while($row = mysql_fetch_array($query)) { /*php */ }

Not sure but is there usually a $row++ at the ned of this section to get the next row

A $row++ would be used in loops to increment a numerical index.

$i =0;
while($i < 10) {
    echo $i;
}

The point there is to increment $i until the boolean check $i < 10 is false .

In our case, however, the $row is an array, returned by mysql_fetch_array . Each iteration of the loop the function returns the next array, which populates the $row .

And because an array is evaluated as true , the while loop will continue to run until there are no more rows, in which case the mysql_fetch_array function returns false and the boolean expression fails.

It's basically a short-cut to doing this:

$row = mysql_fetch_row($result);
while($row == true) 
{
   // ... code that uses $row

    $row = mysql_fetch_row($result);
}

[JRM]

I noticed that you are using mysql_fetch_array , which is numericbut you are calling it out as an associative array .
try:

while ($row = mysql_fetch_assoc($sql));

if this still fails, there is always my old friend var_dump.
try:

var_dump($row);

to see if anything is there in the first place.

[genieuk]

Hi Alti,

Once again thanks for explaining the htmlspecialchars.

Again still nothing.

Do you have error reporting turned on?

Error reporting is turned off on host i use and i have no control on it so i used the code you gave and no errors.

And don't you have to include the sql_con function into that code before you can use it? Or is that a part of some global include?

Also the slq_con(); is used everywhere on my site and works great including my admin where i have view/edit/delete user/view member list etc, so not that but to test i created a db_connect.php file and again no luck.

The sql_con(); function was included in that code you put in last post.

My first guess would be that there is something wrong with the database connection itself. Perhaps you are connecting to the wrong database, or there is some error in the connection that is being suppressed.
That would probably be in your sql_con() function. If you post that here, maybe we could spot the problem in there.

I have been using my admin all day editing/updating stuff without problems using the sql_con(); function and it is definately correct database. I thought maybe something wrong with database but obviously there isn't otherwise i would not be able to view/edit/update database via my admin etc and/or play with my site profile etc.

My second guess would be that there is some character encoding error. You may want to check which encoding is used by our databases and make sure that your code files are encoded the same way.

Not sure what you mean. Do you mean you think the forum is outputting my code wrong? , if so it isn't i checked and is same as i pasted it.

It's a mystery. I mean i can do anything and everything i want and all works fine but not this search query.

Is there anything on the database end i could check via phpmyadmin? , althou everything else works fine not sure if theres anything i could check.

Also to last poster. I checked using mysql_fetc_assoc and MYSQL_BOTH but still nothing just blank page. So i added my site theme includes to see if there was no errors and again just shows theme but not data from database.

i just tried updating and deleting my account in my admin and that works fine so if it was database then i would not be able to do that. Worth testing thou which i did.

errr, well all i can say is thanks to Alti specially your always a great help to me on the forum and always take your time to help and explain everything, much appreciated as always :)

and thanks to everyone else. I guess i will have to knock this on the head.

Thanks

[Atli]

Ok. Best to just start with the basics then, and work our way up. Just to see how far we get before it get's messed up.

Try to put this into a new .php file in the root of your application.

<?php
// Manually connect, bypassing your sql_con() function
$dbLink = mysql_connect('host', 'user', 'pwd') or die(mysql_error());
          mysql_select_db('dbName', $dbLink) or die(mysql_error());

// Execute a query to get all the data, just to see if it works.
$sql = 'SELECT * FROM ` userinformation`';
$result = mysql_query($sql) or die(mysql_error());

// Make sure there is data and that something is printed
// even if there isn't.
if(mysql_num_rows($result) > 0) {
    while ($row = mysql_fetch_array($result));
    {
        echo "<pre>", print_r($row, true), "</pre>";
    }
}
else {
    echo "Query succeeded returned nothing...";
}

mysql_close();
?>

If that works, try replacing the connection info with your sql_con() function. Then add your WHERE clause to the SQL query.

[Atli]

I noticed that you are using mysql_fetch_array , which is numericbut you are calling it out as an associative array.

The mysql_fetch_array function returns both the associative and numerical indices by default.

array [B]mysql_fetch_array[/B] ( [I]resource $result [, int $result_type = MYSQL_BOTH ][/I] )

[JRM]

Thanks. I wasn't aware of that.
The more I learn about this stuff, I realize how little I know!
I wish the original poster didn't give so easily without tearing into this with some diagnostics.
It seems so close...just some simple thing...been there...

[genieuk]

Hi Alti,

I am suprised you replied i thought you probably got fed up.

Ok, i tried your code again blank page, only thing outputed is the <pre></pre> tags in view source.

I then added the code below to turn on error reporting:

ini_set('display_errors', true);
error_reporting(E_ALL + E_NOTICE);

Again no errors or anything just blank with with pre tags in view source.
Again i checked my admin and was able to view/update/edit etc like normal and login to my normal account and update profile etc so database must be fine i am sure.

I am completely and utterly confused. This is why i thought cannot be database as it is displaying my members list in admin and like the rest i said above. I also tried there where clause just to see, worth a try and nothing again.

I don't get it, maybe i got some magic code in my other scripts that i am not aware of lol. Oh dear it ok to laugh lol but it just uses up time and causes problems i could do without lol.

I also checked database details in cpanel and phpmyadmin over and over and all database information is correct. Althou i know that as i can do everything i want on site, but again checked incase.

Thanks again.

UPDATE:

I just tested on local machine with xampp latest version as i use that for testing locally, the same database is in phpmyadmin on local computer again nothing. :-O

[genieuk]

Was also meant to say that my original first post with search box still works, i mean obviously it only displays one result hence this topic being made but i mean it does give me a result. I have had a bad spell put up on me i think.

[Atli]

Ohhh /facepalm
I left a couple of errors in my last code :icon_redface:

Try this one. I've already tested it locally so it should work.

<?php
ini_set('display_errors', true);
error_reporting(E_ALL + E_NOTICE);

$dbLink = mysql_connect('localhost', 'user', 'pwd') or die(mysql_error());
          mysql_select_db('dbName', $dbLink) or die(mysql_error());

$sql = 'SELECT * FROM `userinformation`';
$result = mysql_query($sql) or die(mysql_error());

if(mysql_num_rows($result) > 0) {
    while ($row = mysql_fetch_array($result))
    {
        echo "<pre>", print_r($row, true), "</pre>";
    }
}
else {
    echo "Query succeeded returned nothing...";
}

mysql_close();
?>

[genieuk]

Ohhh /facepalm
I left a couple of errors in my last code :icon_redface:

Try this one. I've already tested it locally so it should work.

<?php
ini_set('display_errors', true);
error_reporting(E_ALL + E_NOTICE);

$dbLink = mysql_connect('localhost', 'user', 'pwd') or die(mysql_error());
          mysql_select_db('dbName', $dbLink) or die(mysql_error());

$sql = 'SELECT * FROM `userinformation`';
$result = mysql_query($sql) or die(mysql_error());

if(mysql_num_rows($result) > 0) {
    while ($row = mysql_fetch_array($result))
    {
        echo "<pre>", print_r($row, true), "</pre>";
    }
}
else {
    echo "Query succeeded returned nothing...";
}

mysql_close();
?>

Hi Alti,

Once again thanks for being so kind to help.

It is showing all users :) with code given above. I also commented out database stuff and added the sql_con(); and it showed all users to :) . My original code i put in netbeans and it says it's fine. I use dreamweaver currently but i wont be using again as i am not kean on it not ideal for programming.

I then used the WHERE clause and great it showed 4 results as it should have done.

So i guess something wrong with my original code althou netbeans says there is no errors, warnings etc and no errors on search and with error reporting turned on. I have also checked the code by eye and i dont spot anything wrong althou i am not an expert as only been learning for several months.

mmm, i will play with my code althou it the same as on forum and to make sure forum has changed it i also copied code from post to see and still same 1 result.

Thanks.

[genieuk]

OMFG. I FIXED IT :):D

Where i have WHILE loop in original script i have

$results_display_2 =

Basically i assigned the results and table in that variable to display further down the page after the form althou i am not sure why that wont work it seems to have fixed it by adding haveing .= instead of just =

YIPPY. Once again thanks for everything.

Maybe you could explain Alti if you understand what i dont wrong.

Thanks as always.

[Atli]

Ahhh, ok :)

The problem was: you were using $results_display_2 to store the results before printing them later in the code. However, you kept overwriting it with the latest results rather than adding it, so when the loop was done, only the last result remained.

A single dot in front of the = changes that. Adds the result rather than overwriting it.

... A single dot :icon_biggrin:

[genieuk]

I know lol. All day i could have been programming other parts i been wanting to do today and all that for a dot lol.

One measley .

But thanks, you have been such a fantastic support to me.

Thanks as always.

[JRM]

Ok ! Great.
A bit more tenacity paid off.
Something simple was clogging the works as usual.
In this case...A DOT !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
I wonder if there is a statistic about what percentage of programmers end up in the loony bin?

[genieuk]

Ok ! Great.
A bit more tenacity paid off.
Something simple was clogging the works as usual.
In this case...A DOT !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
I wonder if there is a statistic about what percentage of programmers end up in the loony bin?

I will be in one myself soon lol at that rate today.

Very rare i have problems and when i do i come here and or ask my friend.

i will be switching from Dreamweaver thou, it's no help personally i dont think to a programmer. When being realtively new i think it's good to have a program that neatens the code as you go along, provide code hints and warns of an error etc it makes the world of difference even to an experienced programmer, plus things like macros etc, all about efficentcie i think and better work flow.

Thanks everyone thou as usual.

[almostbob]

no doubt, earn another -1,
but I want to understand
the original code is syntactically correct,
but without concatenation,
it was correctly doing something different to what was expected,

my head hurts

[Atli]

no doubt, earn another -1,
but I want to understand
the original code is syntactically correct,
but without concatenation,
it was correctly doing something different to what was expected,

my head hurts

Yes. That's what we commonly know as a "bug" (or a Logic Error) :)
The code is syntactically correct, and appears to work fine when it's being written, but it's not doing what it was intended to do. That's what makes them so hard to track down. (Damn bugs! :confused:)

Syntax errors are always caught before the program is released, but logic errors (bugs) are often overlooked and can cause huge problems. The line between the two are kind of blurred in PHP, though, because it is an interpreted language, but in languages like C, Java, and C# there is a very clear distinction. (They need to be compiled before they are used, so you can't even run the program before the syntax errors are cleared... which is also kind of true of PHP, but not as visibly.)

i will be switching from Dreamweaver thou, it's no help personally i dont think to a programmer. When being realtively new i think it's good to have a program that neatens the code as you go along, provide code hints and warns of an error etc it makes the world of difference even to an experienced programmer, plus things like macros etc, all about efficentcie i think and better work flow.

I very much agree. Dreamweaver is nothing special, and certainly not for those of us who spend most of our time coding.

It's a shame. Most of the web-development education I've seen seems to be more about learning how to make Dreamweaver jump through hoops than to learn actual... web-development.

Buuuut, anyhow. I use Netbeans myself... or Aptana. I tend to change my mind every couple of days :P

[JRM]

I've been using Eclipse. It has a handy interface to SVN. All of the usual IDE things are there.
Does NetBeans do the job OK? Do you recommend it?

[genieuk]

I think NetBeans is great. I tried aptana and it is ok but something quite does not do it for me. I would recommend you try NetBeans. I have tried i think everyone or nearly everyone out there and i have not really found one that suits.

Problem is i am used to the color coding in Dreamweaver CS4 and in all other programs it obviously different. I know you can change the color yourself but i can never get it the same as dreamweaver.

I did see a addon for NetBeans to make code color same as Dreamweaver but i cannot find it again now. Anyone got it or know where i can find it please reply. :(

Thanks

[Atli]

I would highly recommend trying NetBeans. Aptana too, for that matter.
They are both excellent IDEs.

If you are mostly programming, NetBeans is probably better for you.
Aptana is more about the design phase of web-development. (Somewhat like Dreamweaver is... but different :))

Problem is i am used to the color coding in Dreamweaver CS4 and in all other programs it obviously different. I know you can change the color yourself but i can never get it the same as dreamweaver.

I got pretty used to the color schema in Dreamweaver too. Kept trying to change my other IDEs to match it, but it never really worked.

I'm over that now :icon_cool:
I like to change the highlighting to match what the php higlighting functions do.