helpchrisplz
asked on
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:
the query i have made (that is working but not showing correctly) :
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
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';
}
}
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>';
}
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
ASKER
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
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
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.
ASKER
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-RE D-SKULL.jp g', 'images/sml_71745-1920x108 0-RED-SKUL L.jpg', 'images/164088-1920x1080-F ractalius- is-AWESOME .jpg', 'images/sml_164088-1920x10 80-Fractal ius-is-AWE SOME.jpg') ,
(2, 2, 'images/164088-1920x1080-F ractalius- is-AWESOME .jpg', 'images/sml_164088-1920x10 80-Fractal ius-is-AWE SOME.jpg', 'images/114288-1920x1080-Y our_Not_Al one.jpg', 'images/sml_114288-1920x10 80-Your_No t_Alone.jp g'),
(3, 3, '0', '0', NULL, NULL),
(4, 4, 'images/177017-1920x1080-B lue_Dragon _ctr9.JPG' , 'images/sml_177017-1920x10 80-Blue_Dr agon_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.c om', 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');
`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-RE
(2, 2, 'images/164088-1920x1080-F
(3, 3, '0', '0', NULL, NULL),
(4, 4, 'images/177017-1920x1080-B
(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.c
(4, 'ben', 'ben', 'ben@ben.com', 32, 'uk', 'male', 'french'),
(5, 'ddaviesd', 'budapest', 'domdavies@hotmail.co.uk',
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Will something like
work in your DB? or do you have to do it like this:
(and a similar question in your other select statement)
WHERE members.UserName OR members.UserEmail LIKE '%".$Search."%'
work in your DB? or do you have to do it like this:
WHERE members.UserName LIKE '%".$Search."%' OR members.UserEmail LIKE '%".$Search."%'
(and a similar question in your other select statement)
ASKER
yes that is doing the trick thx
if (mysql_num_rows($result)>0
{
while ($res=mysql_fetch_array($r
{
var_dump($row);
}
}