query search problems php

hi all.

i have a working search box on my site that returns usernames and email from my members table like the search term.


i am now trying to make it pull in pictures that relate to the results.

please could you help me with my query:

this is my current working query:

$query="SELECT * FROM members WHERE UserName OR UserEmail LIKE '%".$Search."%'"; 
	$result=mysql_query($query) or die ("bad query '$query' : ".mysql_error());
	if (mysql_num_rows($result)>0) {
		  while ($res=mysql_fetch_array($result)) {
		  
			
			echo '<a href="users.php?id=' . $res['MemberID']. '">' . $res['UserName'] . '</a><br>';
			echo $res['UserEmail'].'<br><br>'; 
			
		  }
	}else{
		echo 'No results';
	}	
}

Open in new window


the query i have made (that is working but not showing correctly) :
$query="SELECT  members.MemberID, members.UserName, members.UserEmail, images.imgSmall FROM members, images WHERE images.memberId = members.MemberID AND members.UserName OR members.UserEmail LIKE '%".$Search."%'"; 
	$result=mysql_query($query) or die ("bad query '$query' : ".mysql_error());
	if (mysql_num_rows($result)>0) {
		  while ($res=mysql_fetch_array($result)) {
		  
		 
			echo '<img src='. $res['imgSmall'].'><br>';
			echo '<a href="users.php?id=' . $res['MemberID']. '">' . $res['UserName'] . '</a><br>';
			echo $res['UserEmail'].'<br><br>'; 
			
		  }

Open in new window


here is a screen shot of what the output of the search looks like:



i have 5 members it is just repeating the search 5 times over and not displaying the correct images for the members.
sreasa.png
LVL 1
helpchrisplzAsked:
Who is Participating?
 
shefenConnect With a Mentor Commented:
You need a JOIN

SELECT  members.MemberID, members.UserName, members.UserEmail, images.imgSmall 
FROM members JOIN images ON images.memberId = members.MemberID 
WHERE members.UserName OR members.UserEmail LIKE '%".$Search."%'

Open in new window

0
 
Ray PaseurCommented:
Have you used phpMyAdmin to look at the data base tables and verify that they contain the correct data?  Also you might want to put var_dump() into the while control structure to see the contents of each row that is returned.  Something like this:

if (mysql_num_rows($result)>0)
{
    while ($res=mysql_fetch_array($result))
    {
        var_dump($row);
    }
}
0
 
helpchrisplzAuthor Commented:
my problem is am trying to do a WHERE and then go into a LIKE and i am not sure how to do that.

I just want it to retrieve MemberID, UserName and UserEmail from members and retrieve imgSmall from images where imgSmall  has the same MemberID as the MemberID found in table members. and then use LIKE to only show results relevant to the search

so the user will enter a username or a email into the search box then it will show users with that username with there picture and email





0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
Ray PaseurCommented:
But is the data correct?  Will your WHERE clause find the information?  That is why I want to ask you to verify the contents of the tables.
0
 
helpchrisplzAuthor Commented:
CREATE TABLE IF NOT EXISTS `images` (
  `pictureId` int(4) NOT NULL auto_increment,
  `memberId` int(11) default NULL,
  `imgLarge` varchar(255) default NULL,
  `imgSmall` varchar(255) default NULL,
  `banner` varchar(255) default NULL,
  `bannerSmall` varchar(255) default NULL,
  PRIMARY KEY  (`pictureId`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

--
-- Dumping data for table `images`
--

INSERT INTO `images` (`pictureId`, `memberId`, `imgLarge`, `imgSmall`, `banner`, `bannerSmall`) VALUES
(1, 1, 'images/71745-1920x1080-RED-SKULL.jpg', 'images/sml_71745-1920x1080-RED-SKULL.jpg', 'images/164088-1920x1080-Fractalius-is-AWESOME.jpg', 'images/sml_164088-1920x1080-Fractalius-is-AWESOME.jpg'),
(2, 2, 'images/164088-1920x1080-Fractalius-is-AWESOME.jpg', 'images/sml_164088-1920x1080-Fractalius-is-AWESOME.jpg', 'images/114288-1920x1080-Your_Not_Alone.jpg', 'images/sml_114288-1920x1080-Your_Not_Alone.jpg'),
(3, 3, '0', '0', NULL, NULL),
(4, 4, 'images/177017-1920x1080-Blue_Dragon_ctr9.JPG', 'images/sml_177017-1920x1080-Blue_Dragon_ctr9.JPG', 'images/GreenWave.jpg', 'images/sml_GreenWave.jpg'),
(5, 5, 'images/Desert.jpg', 'images/sml_Desert.jpg', 'images/Penguins.jpg', 'images/sml_Penguins.jpg');






CREATE TABLE IF NOT EXISTS `members` (
  `MemberID` int(11) NOT NULL auto_increment,
  `UserName` varchar(50) NOT NULL,
  `Password` varchar(55) NOT NULL,
  `UserEmail` varchar(55) NOT NULL,
  `Age` int(11) NOT NULL,
  `Location` varchar(55) NOT NULL,
  `Gender` varchar(55) NOT NULL,
  `Language` varchar(55) NOT NULL,
  PRIMARY KEY  (`MemberID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

--
-- Dumping data for table `members`
--

INSERT INTO `members` (`MemberID`, `UserName`, `Password`, `UserEmail`, `Age`, `Location`, `Gender`, `Language`) VALUES
(1, 'admin', 'admin', 'admin@admin.com', 20, 'germany', 'male', 'french'),
(2, 'chris', 'chris', 'chris@chris.com', 22, 'Yarm', 'male', 'english'),
(3, 'christopher', 'christopher', 'christopher@christopher.com', 32, 'uk', 'male', 'french'),
(4, 'ben', 'ben', 'ben@ben.com', 32, 'uk', 'male', 'french'),
(5, 'ddaviesd', 'budapest', 'domdavies@hotmail.co.uk', 21, 'Middlesbrough', 'male', 'english');
0
 
sgvillCommented:
Will something like

WHERE members.UserName OR members.UserEmail LIKE '%".$Search."%' 

Open in new window


work in your DB?  or do you have to do it like this:

WHERE members.UserName LIKE  '%".$Search."%'  OR members.UserEmail LIKE '%".$Search."%' 

Open in new window


(and a similar question in your other select statement)
0
 
helpchrisplzAuthor Commented:
yes that is doing the trick thx
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.