kickstart

December 2, 2011

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

December 2, 2011

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

December 2, 2011

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

December 1, 2011

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

December 1, 2011

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

December 1, 2011

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

December 1, 2011

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

December 1, 2011

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

December 1, 2011

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

December 1, 2011

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

December 1, 2011

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

September 21, 2011

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

September 21, 2011

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

September 21, 2011

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

September 14, 2011

I think I was right in choosing LEFT JOIN then, because I'd want a result to show up even if certain information for a contact is missing, such as telephone etc.

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

September 14, 2011

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

August 26, 2011

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

August 26, 2011

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

August 26, 2011

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

August 17, 2011

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

August 12, 2011

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

August 10, 2011

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
}
}
?>

August 10, 2011

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

August 10, 2011

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

August 10, 2011

ONE LAST QUESTION HOW TO GET THE DATE IN THE FORMAT OF MM/DD/YYYY BECAUSE I HAVE TO COMPARE IT WITH THE MENTIONED FORMAT DATE WITH THE NOW FUNCTION

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

Sign In

kickstart

Posts

Joined

Last visited

Content Type

Profiles

Forums

Posts posted by kickstart

Can you "eval" a word?

Avoidng Undefined Variable Notice

MYSQL in_array?

How do I Use data from 2 tables

SQL query problem

SQL query problem

PHP GD and true type fonts

Impossible WHERE clause

SQL Help

SQL query problem

PHP GD and true type fonts

query optimization

query optimization

Stored Procedure returning only 'false' statments

Help with query

Help with query

JOIN problem, combined with PHP loop

recent activity architecture

Join regardless of match?

MySQL statement not working properly?

Trying to query 3 tables using INNER JOIN

Comparison between 2 tables

Comparison between 2 tables

Comparison between 2 tables

EASRY QUESTION OF THE DAY??

Browse

Activity

Important Information