tommyda

thanks but i get the error "Column 'userID' in on clause is ambiguous"

Can the following code be shortened to use just one query? At the moment it grabs a list of contacts from the db then checks the database for the users details, i know there is a way to combine the 2 querys into one but i cant remember how its done, any help would be much appreciated. Thanks Tommy $getcontacts = mysql_query("SELECT contactID FROM usergroups WHERE groupID = '$_GET[groupID]'")or die(mysql_error()); if(!mysql_num_rows($getcontacts)> 0) echo"No contacts in this group!"; else while($contact = mysql_fetch_array($getcontacts)) { $getcontact = mysql_query("SELECT * FROM users WHERE userID = '$contact[contactID]'")or die(mysql_error()); $user = mysql_fetch_assoc($getcontact); echo"$user[fullName] - $user[username] - $user[email] - $contact[contactID]<br/>"; };

I have a site with video listings and tags the table structure is Videolistings id name description Tags tag vlid I would like to use mysql's fulltext search function to search both the videolistings[name,desc] and tags assigned to that videolisting id. is this possible? If you could point me in the right direction that would be much appreciated. Regards Tommy0000

Can I see your source code?

Here is my query. <?php $query = mysql_query(" SELECT typeid, (select count(typeid) from ratings) / (select count(distinct typeid) FROM ratings) AS avg_num_votes, (select avg(rating) from ratings) AS avg_rating, count(typeid) as this_num_votes, avg(rating) as this_rating FROM ratings GROUP BY typeid ")or die(mysql_error()); ?> Im getting this error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select typeid, ((avg_num_votes * avg_rating) + (this_num_votes * this_rating) / ' at line 10

please disregard this post ive sorted it

Hi I have 3 tables, one with a list of genres, one with movie listings and one to assign genres to the movie listings. Genres id, name Videolistings id, name, title, img Assign type, typeid, vlid I would l to pull the list of genres and all the movie listing details in the same query, I have managed to achieve this but only using 3 separate queries but it looks messy and I know there must be a more efficient way of doing this. currently I am selecting all the rows from the assign table where type=genre and vlid=1 then in a while loop I am selecting * from videolistings to get the info and selecting name from genres. <?php $getgenrelist=mysql_query("SELECT * FROM assign WHERE type='genre' AND typeid='$vlid'") or die(myslq_error()); while($row = mysql_fetch_array($getgenrelist)) { $getvideo = mysql_query("SELECT * FROM videolistings WHERE id ='$row[vlid]' "); $video = mysql_fetch_array($getvideo); $getgenre = mysql_query("SELECT name FROM genres WHERE id = '$row[typeid]'") while($genre= mysql_fetch_array($getgenre)) { $genres.=$genre['name']; }; echo $Video name $img $genres....... }; }; ?> So if anyone can help that would be much appreciated. Thanks

Wow it works, thanks I understand the query now. Thanks again/.

Thanks for the help. Mchl could you possibly explain the query. i think ive got it but i dont understand what the .m is for

is there a way to select * from a table where the row id matches any one of the numbers in an array? I have a table called Genres{ id, vlid, name } And a table called Movies(id, name, releasedate) The genres are assigned to the movies using a the following table Assign{ vlid,typeid, type } What i want to do is select all the movie id;s from the assign table then pull the relevant movies using the vlids. I do have a function but I have a feeling its is poorly coded, plus it doesnt work very well. MoviesByAssign("genre", 1, 0, 30); should pull the first 30 movies assigned to the genre id "1" <php function MoviesByAssign($type, $typeid, $start, $limit){ $moviesingenre = array(); $result = mysql_query("SELECT vlid FROM assign WHERE type ='$type' AND typeid='$typeid'")or die(mysql_error()); while($row = mysql_fetch_array($result)) { echo $row['vlid'].'-'; $moviesingenre[] = $row['vlid']; } $result = mysql_query("SELECT * FROM videolistings ORDER BY releasedate DESC LIMIT $start, $limit"); $output=''; while($video = mysql_fetch_array($result)) { if(in_array($video['id'], $moviesingenre)) { $date = date("2/m/Y",$video['releasedate']); $num = GetMovieRating($video['id']); $expl = explode('-',$num); $round1 = round($expl[0],1); $rounded = round($expl[0]); if(empty($video['img'])){$img= $baseurl.'/images/x.gif';}else{$img= $video['img'];}; $output.= ' <div class="lholder"> <a href="'.$baseurl.'/videos/'.$video['slug'].'_'.$video['id'].'" title="'.ucwords($video['name']).'"> <img class="limg" src="'.$img.'" border="0"/></a> <div class="linfo"> <a href="'.$baseurl.'/videos/'.$video['slug'].'_'.$video['id'].'" title="'.ucwords($video['name']).'"><strong>'.ucwords($video['name']).'</strong></a> <ul class="linkrow"> <li>Release Date: '.$date.'</li> <li> Rating: <strong>'.$round1.'</strong> / 5 ('.$expl[1].' Votes) <br/><img src="'.$baseurl.'/img/stars_'.$rounded.'.gif"/></li></ul></div></div>'; }//END IF };//ND WHILE return $output; };//END FUNCTION ?> Can anyone please help

Worked it out, Ill explain what i did for any one else having the same problem. Originally the field was a varchar but for ORDER BY to work it needs to be set as INT.

I have a list of movie titles in a database each with a time stamp of the release date The query: <?php $getvideos = mysql_query("SELECT * FROM videolistings ORDER BY releasedate DESC")or die(mysql_error());?> But for some reason the videos are not listing in descending order, EG: the videos on this page http://bit.ly/L8h7F are displaying 2 results from 2000 first then more recent ones from 08 and 09 after. Here is the while loop: <?php while ($video = mysql_fetch_array($getvideos)) { echo '<div style="width:280px; float:left; border:1px dotted #666; padding:5px; margin:5px; "><a href="'.$baseurl.'/videos/'.$video['slug'].'" title="'.ucwords($video['name']).'"><img width="80px"; height="110px"; src="'.$video['smlimg'].'" border="0"/></a></div>'; };?> Can anyone please help me EDIT: I have echo'd out the timestamps for each listing.

Thanks rarebit, but I beat you to it.

Its ok, Ive got it if ($_POST['imageupload']=='upload' && isset($_FILES['new_image'])){.....

Im building an admin panel with an image upload function, but I have set it to only run the image upload script if the file field is set. For some reason the function is running even if it is not set, which is obviously giving me errors. I also created 2 radio buttons one next to an input field named "url" and the other next to the file field named "upload" I tried to change the upload function to only run when radio button "upload" is selected which also doesnt work and runs either way. The form code: <tr> <td valign="top">Image Url</td> <td><label> <input type="radio" name="imageupload" value="url"><input name="imageurl" type="text" id="slug" size="70" /> </label></td> </tr> <tr> <td valign="top">Image Upload</td> <td><label> <input type="radio" checked name="imageupload" value="upload"> <input name="new_image" type="file" id="new_image" size="60" /> </label></td> </tr> The Upload Function: <?php if ($_POST['imageupload']='upload' && isset($_FILES['new_image'])){ $imagename = $_FILES['new_image']['name']; $source = $_FILES['new_image']['tmp_name']; $target = "../images/".$imagename; move_uploaded_file($source, $target); $imagepath = $imagename; $save = "../images/" . $imagepath; $file = "../images/" . $imagepath; list($width, $height) = getimagesize($file) ; $modwidth = 120; $diff = $width / $modwidth; $modheight = $height / $diff; $tn = imagecreatetruecolor($modwidth, $modheight) ; $image = imagecreatefromjpeg($file) ; imagecopyresampled($tn, $image, 0, 0, 0, 0, $modwidth, $modheight, $width, $height) ; imagejpeg($tn, $save, 100) ; $save = "../images/sml_" . $imagepath; $file = "../images/" . $imagepath; list($width, $height) = getimagesize($file) ; $modwidth = 80; $diff = $width / $modwidth; $modheight = $height / $diff; $tn = imagecreatetruecolor($modwidth, $modheight) ; $image = imagecreatefromjpeg($file) ; imagecopyresampled($tn, $image, 0, 0, 0, 0, $modwidth, $modheight, $width, $height) ; imagejpeg($tn, $save, 100) ; $fullimagepath = $baseurl.'/images/'.$imagepath; }else { $fullimagepath = $_POST['imageurl']; }; ?> Any help would be much appreciated. Thanks for reading.

Perfect thanks for your time, I learned a lot from this.

Cags Thanks for the replies. Im not trying to "select the genres for a movie" I have a page to insert movie listings and on that page I have a multiple select box with all the genres in table"genres" listed. I select the genres I would like to assign to this movie and when I save it they are inserted into table"assign". Now I am creating the "edit movie" page and would like to have the same list as in "insert movies" but with the genres that are already assigned I would like them to be selected. Is your last post going to achieve this or are you suggestion I do it some other way. I have no idea what that sql query means or does. Thanks

I changed it to == but im getting the same results

Im stuck on this one so if anyone could help that would be much appreciated. I have a database table called genres which is a list of movie genres and a table called assign. In assign I have the genre id and the movie id. I am trying to create a multiple select list with all of the genres and if one of the genres is assigned to this movie to add selected="selected" to the option. while($genre=mysql_fetch_array($getgenres)) { while($assigned=mysql_fetch_array($getassignedgenres)) { if($genre['id']=$assigned['typeid']) { echo '<option selected="selected" style="padding:5px;" value="'.$genre['id'].'">'.$genre['genre'].'</option>'; } else { echo '<option style="padding:5px;" value="'.$genre['id'].'">'.$genre['genre'].'</option>'; }; }; }; At the moment there are 3 genres assigned to this movie but its just giving me the same 3 instead of each one. Please help

Thanks man I should have thought of that.

I am creating a search function for a customer database and have four <select> boxes with search values. I would like the user to be able to choose one or more of the options and search the database. I started writing the following code but if I carry on it will take a long time and im sure there must be a way of using .= or something similar to get the job done. if (isset($_POST['archiveoption']) && !isset($_POST['deliveryoption']) && !isset($_POST['paymentoption']) && !isset($_POST['status'])) { $query = "SELECT * FROM customers WHERE archiveoption='$_POST[archiveoption]'"; } if (isset($_POST['archiveoption']) && isset($_POST['deliveryoption']) && !isset($_POST['paymentoption']) && !isset($_POST['status'])) { $query = "SELECT * FROM customers WHERE archiveoption='$_POST[archiveoption]' AND deliveryoption='$_POST[deliveryoption]'"; } if (isset($_POST['archiveoption']) && !isset($_POST['deliveryoption']) && isset($_POST['paymentoption']) && !isset($_POST['status'])) { $query = "SELECT * FROM customers WHERE archiveoption='$_POST[archiveoption]' AND deliveryoption='$_POST[deliveryoption]'"; } I thought I could use something like if(isset($_POST['archiveoption'])) {$query .= "AND archiveoption='$_POST[archiveoption]'"}; if(isset($_POST['deliveryoption'])) {$query .= "AND deliveryoption='$_POST[deliveryoption]'"}; etc... The problem im having is that if there is only one box selected the query has "AND ..." which wont work. Please if anyone can help me out i would appreciate it very much.

Youd be better off creating a table called cars and adding fields (carname) Then you can select * from cars and echo the names in a while loop

try mysql_fetch_array instead of assoc

I am building a search function using the following code: $results = mysql_query("SELECT * , MATCH(name,description,keywords,manufacturerid) AGAINST('+$search' IN BOOLEAN MODE) AS score FROM products WHERE MATCH(name,description,keywords,manufacturerid) AGAINST('+$search' IN BOOLEAN MODE) AND imageurl !='' ORDER BY score DESC")or die(mysql_error()); The problem I am having is that im getting more results when I search for the word "table" than I am for the plural "tables" I would preferably like to display both results for the plural and single variations of each keyword searched. I read on another forum that this can be solved using REGEXP but i have no idea how to implement it. Any help would be much appreciated. Thanks

have a look here http://www.google.co.uk/search?hl=en&safe=off&client=firefox-a&rls=org.mozilla:en-GB:official&hs=KSi&ei=TDOfSoCsDsP4-AbypcDzDw&sa=X&oi=spell&resnum=0&ct=result&cd=1&q=open+source+wysiwyg+editors&spell=1