kickstart

Hi Think what you want is something like this:- http://php.net/manual/en/language.variables.variable.php Not tested but something like this:- <?php for ($j = 1; $j < 4; ++$j) { $fieldname = $field.$j; $arrayvar[$j] = $$fieldname; } ?> All the best Keith

Hi Assign a default value to $user_name at the start of the script. You are using its value potentially before it has been assigned anything at all. All the best Keith

Hi In the case you have there it is going to look for '1,2', and not find it as it the only option is the string '1,2,3,4'. You would need to explode Fieldname into an array, and check for each member of the array being withing (1,2,3,4) without the quotes around them. Something like:- $sql = 'SELECT * FROM SomeTable WHERE '.explode(' IN (1,2,3,4) OR ',FieldName).' IN (1,2,3,4) '; (note the above will screw up if FieldName is empty). All the best Keith

Hi You can do it in a single SELECT statement (something like as follows). SELECT * FROM images LEFT OUTER JOIN user_deleted ON images.file_name = user_deleted.file_name WHERE user_deleted.file_name IS NULL Note, it would probably be better to have 2 columns in the images table, one being an auto incremented id and the other the file name. And refer to the id field in the user_deleted table. A join on simple numeric fields like this should be quite a bit more efficient (both in speed and also in how much storage is used once you factor in all the possible rows on user_deleted). All the best Keith

Hi You could use JOINs instead:- SELECT * FROM MOVIE INNER JOIN MOVIEGENRE ON MOVIE.MOVIEID = MOVIEGENRE.MOVIEID INNER JOIN GENRE ON MOVIEGENRE.GENREID = GENRE.GENREID INNER JOIN USERSGENRE GENRE.GENREID = USERSGENRE.GENREID WHERE USERID = 1 If you do not need anything from the GENRE table then you could do without JOINing that one in. You will get duplicate movies this way but a DISTINCT can solve that. All the best Keith

Hi You appear to already have a table linking users and genres. Presume usersgenre will have multiple rows for each user, one for each genre they like. You need a similar table linking movies to genres. All the best Keith

Hi No error message, just getting small square (90%) or a blank (other 10%) for the characters. My test code is as follows (loops through from 0 to 255 and creates a small image with that character in the middle of it):- <?php $width = 1; $font = 0; $ButtonUnit = 50; $widthDisplay = $width * $ButtonUnit; $font = 'webdings.ttf'; for($xCnt=0;$xCnt<=255;$xCnt++) { $char = $xCnt; $ImageSaved = DrawButton($char,array(255,255,255),array(255,165,0),$font,$widthDisplay,$ButtonUnit,15,5,array(0,0,0)); } function DrawButton($ButtonText,$ForeGroundColour=array(255,255,255),$ButtonColour=array(18,52,86),$font = '', $Width = 0, $Height = 0, $fsize=24, $CornerRadius = 50, $FadeToColour=array(255,255,255)) { global $Language; $CacheLocation = "Images/Names/"; $fontarray = explode('.',$font); $FullFilePath = $CacheLocation.$fontarray[0].'_'.$ButtonText.".gif"; if (file_exists($FullFilePath)) //if (false) { // Whoopee Doo } else { $UsedText = html_entity_decode(chr($ButtonText)); if ($UsedText == '') $UsedText = '?'; echo "$ButtonText $font<br />"; $font = (($font == '') ? 'Tuffy.ttf' : $font); echo "$ButtonText $font<br />"; $tb=calculateTextBox($fsize, 0, $font, $UsedText); //gets an array of numbers...some can be used to define image size $Width = (($Width==0) ? ceil($tb["width"]*1.4) : $Width);//create a space for lowercase letters that go below base $Height = (($Height==0) ? ceil($tb["height"]*1.4) : $Height); $LeftPos = ceil(($Width - $tb["width"]) / 2); $TopPos = $tb["top"] + ceil(($Height - $tb["top"]) / 2); $Image = imagecreate ($Width , $Height ); $background_color = imagecolorallocate($Image, 0, 0, 0); $button_color = imagecolorallocate($Image, $ButtonColour[0], $ButtonColour[1], $ButtonColour[2]); imagecolortransparent($Image, $background_color); $color0=($ButtonColour[0]-$FadeToColour[0])/($CornerRadius*2); $color1=($ButtonColour[1]-$FadeToColour[2])/($CornerRadius*2); $color2=($ButtonColour[2]-$FadeToColour[2])/($CornerRadius*2); $SpotCentreX = array($CornerRadius, $Width - $CornerRadius - 1, $Width - $CornerRadius - 1, $CornerRadius); $SpotCentreY = array($CornerRadius, $CornerRadius, $Height - $CornerRadius - 1, $Height - $CornerRadius - 1); for ($i=($CornerRadius*2);$i>0;$i=$i-1) { $red=$ButtonColour[0]-floor($i*$color0); $green=$ButtonColour[1]-floor($i*$color1); $blue=$ButtonColour[2]-floor($i*$color2); $col= imagecolorallocate($Image, $red, $green, $blue); imagefilledellipse($Image , $SpotCentreX[0], $SpotCentreY[0], $i, $i, $col); imagefilledellipse($Image , $SpotCentreX[1], $SpotCentreY[1], $i, $i, $col); imagefilledellipse($Image , $SpotCentreX[2], $SpotCentreY[2], $i, $i, $col); imagefilledellipse($Image , $SpotCentreX[3], $SpotCentreY[3], $i, $i, $col); imageline($Image, $SpotCentreX[0], $SpotCentreY[0]-intval($i/2), $SpotCentreX[1], $SpotCentreY[1]-intval($i/2), $col); imageline($Image, $SpotCentreX[1]+intval($i/2), $SpotCentreY[1], $SpotCentreX[2]+intval($i/2), $SpotCentreY[2], $col); imageline($Image, $SpotCentreX[3], $SpotCentreY[3]+intval($i/2), $SpotCentreX[2], $SpotCentreY[2]+intval($i/2), $col); imageline($Image, $SpotCentreX[0]-intval($i/2), $SpotCentreY[0], $SpotCentreX[3]-intval($i/2), $SpotCentreY[3], $col); } imagefilledrectangle($Image , $SpotCentreX[0] , $SpotCentreY[0] , $SpotCentreX[2] , $SpotCentreY[2] , $button_color ); //imagefilledellipse($Image , floor($Height/2), floor($Height/2), floor($Height/2)*2, floor($Height/2)*2, $button_color); //imagefilledellipse($Image , ($Width - floor($Height/2) - 1), floor($Height/2), floor($Height/2)*2, floor($Height/2)*2, $button_color); //imagefilledrectangle($Image , floor($Height/2) , 0 ,($Width - floor($Height/2) - 1) , floor($Height/2)*2 , $button_color ); $foreground_color = imagecolorallocate($Image, $ForeGroundColour[0], $ForeGroundColour[1], $ForeGroundColour[2]); imagettftext($Image, $fsize, 0, $LeftPos , $TopPos, $foreground_color, $font, $UsedText); $FullFilePath = $CacheLocation.$font.'_'.$ButtonText.".gif"; $imageSuccess = imagegif($Image,$FullFilePath); } return($FullFilePath); } function calculateTextBox($font_size, $font_angle, $font_file, $text) { $box = imagettfbbox($font_size, $font_angle, $font_file, $text); if( !$box ) return false; $min_x = min( array($box[0], $box[2], $box[4], $box[6]) ); $max_x = max( array($box[0], $box[2], $box[4], $box[6]) ); $min_y = min( array($box[1], $box[3], $box[5], $box[7]) ); $max_y = max( array($box[1], $box[3], $box[5], $box[7]) ); $boxwidth = ( $max_x - $min_x ); $boxheight = ( $max_y - $min_y ); $left = abs( $min_x ) + $boxwidth; $top = abs( $min_y ) + $boxheight; // to calculate the exact bounding box i write the text in a large image if ($boxwidth == 0) $boxwidth = 50; if ($boxheight == 0) $boxheight = 50; $img = imagecreatetruecolor( $boxwidth << 2, $boxheight << 2 ); // << bitwise shift $white = imagecolorallocate( $img, 255, 255, 255 ); $black = imagecolorallocate( $img, 0, 0, 0 ); imagefilledrectangle($img, 0, 0, imagesx($img), imagesy($img), $black); // for sure the text is completely in the image! imagettftext( $img, $font_size, $font_angle, $left, $top, $white, $font_file, $text); // start scanning (0=> black => empty) $rleft = $w4 = $boxwidth<<2; $rright = 0; $rbottom = 0; $rtop = $h4 = $boxheight<<2; for( $x = 0; $x < $w4; $x++ ) for( $y = 0; $y < $h4; $y++ ) if( imagecolorat( $img, $x, $y ) ) { $rleft = min( $rleft, $x ); $rright = max( $rright, $x ); $rtop = min( $rtop, $y ); $rbottom = max( $rbottom, $y ); } // destroy img and serve the result imagedestroy( $img ); return array( "left" => $left - $rleft, "top" => $top - $rtop, "width" => $rright - $rleft + 1, "height" => $rbottom - $rtop + 1 ); } ?> All the best Keith

Hi Not sure why you are getting an impossible where clause, but if it did work you would get a massive number of results as it is cross joining against products_to_categories. All the best Keith

Hi Using decent indexes those JOINs should be fairly efficient (although I far prefer using JOINs coded as awjudd has suggested above). All the best Keith

Hi This suggests an error in how the database is designed. You probably should have another table linking genres to films. Can't really see an elegant way of doing it with how you have it now. If you really must then change the / to a , separating the movie genres and then use the following (and it likely will be inefficient). SELECT * FROM MOVIE WHERE FIND_IN_SET( ( SELECT GENRENAME FROM GENRE LEFT JOIN USERSGENRE USING ( GENREID ) WHERE USERID =1 ) , MOVIEGENRE) All the best Keith

Hi Not that often I play with these (and normally just a single font I use). However I am trying to generate an image with a ttf bit of text on it. No problem using Tuffy.ttf (the normal one I use), but I want to use some arrow characters which it doesn't support. I have tried using Wingding and Webdings but neither work with GD. Am I missing something (hopefully something obvious)? All the best Keith

Hi Not sure of all the field names (and depending on exactly what you want back there may well be a better way to do this), but as an example something like this should do it. SELECT * FROM `sdb`.`Student` s INNER JOIN addr a ON s.zipcode = a.zipcode INNER JOIN (SELECT SID, MAX(SCourseID) AS MaxCourseID FROM enrol GROUP BY SID) Deriv1 ON Deriv1.SID = s.SID INNER JOIN enrol en ON Deriv1.SID = en.SID AND Deriv1.MaxCourseID = en.sCourseID INNER JOIN SCourse sc ON en.SCourseID = sc.SCourseID INNER JOIN course c ON sc.CourseID = c.CourseID INNER JOIN fenrol fe ON c.CourseID = fe.CourseID WHERE s.SName = '$SName' AND c.CourseID='$CourseID' AND sc.WeekDay='$WeekDay'"; By the way, generally I would say do not use SELECT * All the best Keith

Hi To limit it to a single row you need to chose a particular course for each row (possibly the MAX course) and use a suitable aggregate function and GROUP BY. All the best Keith

Hi I can't see anything obvious, but I expect the issue is with the actual select failing. Having you tried copying the SELECT statements out and trying them each individually directly on the database? All the best Keith

An INNER JOIN would still bring back a matching row with blank fields. What it won't do (and why you use an OUTER JOIN) is bring back a row when there is no matchig JOINed row. All the best Keith

Hi INNER JOIN brings back a row when there are matching rows on both tables. LEFT OUTER JOIN brings back a row even if there is no matching row on the right hand table. For example, say you had a table of people, and a table of cars. Using an INNER JOIN you would join the 2 tables together to get a list of people who had cars, with someone who had 2 cars appearing twice in the list but someone who didn't have a car wouldn't be on the list. Using a LEFT OUTER JOIN you could include the people without a car, with the car details being null on the row brought back about them All the best Keith

Hi Every row will have the columns from both tables in it. Therefore what you need to do is only output the fields from the first table when they change, which is what Muddy_Funsters code does (although there is a couple of missing table td tags). All the best Keith

Hi 2nd option is the most correct way to do it (avoids duplicating data). However if they are busy tables I would be tempted to use the 1st option. All the best Keith

Hi Seems what you need is a FULL OUTER JOIN, which mysql doesn't support. You could possibly emulate it using a LEFT OUTER JOIN and UNIONing the results with a RIGHT OUTER JOIN. However to be honest I would be a bit fearful of doing something like this on a bulk delete. All the best Keith

Hi You appear to have reversed the comparison operator between your original statement and you new one. Also you probably want a GROUP BY clause. All the best Keith

Hi If you want a row returned when one of the joined tables doesn't have a matching row then use an outer join SELECT classes.id, classes.state, classes.date AS created, classes.city, users.`name` AS instructor, courses.title AS course FROM classes INNER JOIN users ON users.id = classes.instructor_id LEFT OUTER JOIN courses ON courses.id = classes.courseid All the best Keith

Hi Interesting that it manages something but that won't work as it will not match the first or last items in the : separated list (unless you have a colon at the start and end of the list). If you do use a loop then do it like this:- <?php $query = "SELECT `favorites` FROM `users` WHERE `id`='currentuserprofile'"; $result = mysql_query($query); while($row = mysql_fetch_assoc($result)) { $favorites = explode(':', $row['favorites']); $query = "SELECT * FROM `items` WHERE `id` IN (".implode(",",$favorites).")'$value'"; $result = mysql_query($query); while($row = mysql_fetch_assoc($result)) { echo stuff } } ?>

Hi Don't think you can do that but it will mess up with 1 matching 10, 11, 12, etc. If you could you would want something like:- SELECT * FROM `items` WHERE CONCAT('%',`ID`,'%') LIKE (SELECT `favorites` FROM `users` WHERE `id`='5') Or doing it using a JOIN SELECT * FROM users INNER JOIN items ON CONCAT('%',items.ID,'%') LIKE favorites WHERE users.id='5' Neither work (without looking into it too much, don't think you can concatenate in wildcards to a column value to use a LIKE). All the best Keith

Hi Further to the last post, you can do it like this (but really advise you not to). SELECT UserName, CityArea FROM (SELECT DISTINCT UserName, FavoriteId FROM (SELECT UserName, SUBSTRING_INDEX( SUBSTRING_INDEX( favorites, ":", b.ournumbers ) , ":", -1 ) AS FavoriteId FROM users a, ( SELECT hundreds.i *100 + tens.i *10 + units.i AS ournumbers FROM integers AS hundreds, integers AS tens, integers AS units)b) AS Deriv1 WHERE FavoriteId != "") Deriv2 INNER JOIN items ON Deriv2.FavoriteId = items.id This is using a table of integers (one column, 10 rows with values 0 to 9) to get a load of numbers, and then using that number with substring_index to get all the possible indexes. Exclude the ones where the id is blank and use distinct to chop any duplicates. Then join that against items to get the matching rows from items. As you can see not readable, almost certainly hideously inefficient but technically possible. FAR better to redesign the tables though. All the best Keith

Also, comparing with a date in the format MM/DD/YYYY is a pain. You will have to reformat that anyway. Better to reformat that into the mysql date format. All the best Keith