Help - Search - Members - Calendar
Full Version: error in sql syntax
ieXbeta Board > Tech > Developer Center
Dutch2005
CODE
select id,title,year,imdb,genre,poster,rating,actor,disc,quality,type,resx,resy,notes,r
elease,director from movies order by title ASC limit 0,10
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 'release,director from movies order by title ASC limit 0,10' at line 1


mysql structure
CODE

CREATE TABLE `movies` (
`id` INT( 255 ) NOT NULL AUTO_INCREMENT ,
`imdb` VARCHAR( 7 ) NOT NULL ,
`title` VARCHAR( 155 ) NOT NULL ,
`genre` SET( 'Action', 'Adult', 'Adventure', 'Animation', 'Comedy', 'Crime', 'Documentary', 'Drama', 'Erotic', 'Family', 'Fantasy', 'Flim-Noir', 'Horror', 'Musical', 'Mystery', 'Romance', 'Sci-Fi', 'Short', 'Thriller', 'War', 'Western', 'other' ) DEFAULT 'other' NOT NULL ,
`year` YEAR( 4 ) DEFAULT '0000' NOT NULL ,
`poster` VARCHAR( 255 ) NOT NULL ,
`rating` CHAR( 3 ) NOT NULL ,
`actor` VARCHAR( 255 ) NOT NULL ,
`disc` INT( 2 ) NOT NULL ,
`quality` INT( 1 ) NOT NULL ,
`type` SET( 'ASF', 'DAT', 'DivX', 'DVD', 'M2V', 'MOV', 'MPEG', 'RAM', 'SVCD', 'VCD', 'WMV', 'XVCD' ) NOT NULL ,
`resx` VARCHAR( 4 ) ,
`resy` VARCHAR( 4 ) ,
`notes` TEXT,
`release` VARCHAR( 255 ) NOT NULL ,
`Director` VARCHAR( 255 ) NOT NULL ,
PRIMARY KEY ( `id` )
) TYPE = MYISAM COMMENT = 'PMDB'
Sinbad
can't remember if it matters or not, but your structure has Director with a capital D, and your query has a lower case d.
Dutch2005
i tried that, changed to lower capitial and it didn't work...
madTaMsKi
What about simplifying your query a little to this, do you still get the error? (copy and paste below, do not re-type)

CODE
select id,title,year,imdb,genre,poster,rating,actor,disc,quality,type,resx,resy,notes,r
elease,director from movies order by title
Dutch2005
well i kinda have no idea, i mean, its not a query i made up myself, its a query from a scritpt..


http://xtremeweb-hosting.net/pmdb/

thats the site...

i plan on use-ing it so i got a online thingy of what dvd's/movies i own...


http://freshmeat.net/projects/pmdb/ (creators website), alan, no longer works with it, have NOT yet contacted the other 2...
quantumAlpha
change the capital in Director along with a few other column names, that could be throwing it off as well
madTaMsKi
Dunno about mysql, but in standard SQL column names are not case-sensitive.

Dutch2005
select id,title,year,imdb,genre @ site script..


id, imdb, title, genre..... @ db, could this be a problem?

QUOTE

r version for the right syntax to use near 'release,director from movies o





is it, selecting 'release, or, release?


if its 'release, its DOH!, its not existing...


and for that suggested query you gave..


QUOTE

SQL-query: Documentatie

SELECT id, title, year, imdb, genre, poster, rating, actor, disc, quality,
TYPE , resx, resy, notes, release, director
FROM movies
ORDER BY title
LIMIT 0 , 30


QUOTE

MySQL retourneerde: Documentatie
#1064 - 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 'release , director from movies order by title LIMIT 0, 30' at line 1
Phonics Monkey
This is where syntax highlighting is uber handy...

Two of your column names are control verbs, type and release

Your query should look like this:
CODE
select id, title, year, imdb, genre, poster, rating, actor, disc, quality, `type`, resx, resy, notes, `release`, director from movies order by title ASC limit 0,10




Note ` is not a single quote, it's the other (Tilde key) one.
Dutch2005
think that should work, in phpmyadmin it returned NO error, now to check for the line in the source that does the sql statement :/


just gonna send the other 2 admin's the code fixes and hope they know were to fix thre error's smile.gif ;-)
Phonics Monkey
QUOTE(Dutch2005 @ Dec 31 2006, 12:52) *

think that should work, in phpmyadmin it returned NO error, now to check for the line in the source that does the sql statement :/


just gonna send the other 2 admin's the code fixes and hope they know were to fix thre error's smile.gif ;-)

Use This: X-Find

It'll search all the files in the target folder for a line of text, and then tell you exactly where it is (in about 4 sec).
Dutch2005
well sure, 1 problem..

it uses variables..


select id, title, year, imdb, genre, poster, rating, actor, disc, quality, `type`, resx, resy, notes, `release`, director from movies order by title ASC limit 0,10


could be..

select $id, $title, $year, $imdb, $genre, etc.....


if you would be so nice to download the source your self and check were the problme is, it would be sooo cool smile.gif ^^

i found this..
list_movie.phtml
CODE

$select_result=db_query($select_query,$dbcon);
$i=1;
while(list($id,$title,$year,$imdb,$genre,$poster,$rating,$actor,$disc,$quality,$type,$resx,$resy,$notes,$release)=mysql_fetch_array($select_result)){
    $pdf->Cell(40, 10, $i." - ".$title." (".$year.") - ". $genre. " (".$disc." CD)");
    $pdf->Ln(5);
    $i++;
}
$pdf->Output();
?>




ne thing i can change at it?
Phonics Monkey
Same thing would (/should) apply:
CODE
$disc, $quality, `$type`, $resx, $resy, $notes, `$release`)
Dutch2005
ok i'll check t out right away, hope it works...


ok this what i made of it..

CODE

<?
require('fpdf151/fpdf.php');
define('FPDF_FONTPATH','font/');
include("config.phtml");
include("function.phtml");

class PDF extends FPDF
{
//Current column
var $col=0;
//Ordinate of column start
var $y0;

function Header()
{
    //Page header
    global $title;
    $title = 'Movies List - '.$this->PageNo();

    $this->SetFont('Arial','B',15);
    $w=$this->GetStringWidth($title)+6;
    $this->SetX((210-$w)/2);
    $this->SetDrawColor(0,80,180);
    $this->SetFillColor(203,232,255);
    $this->SetTextColor(0,0,255);
    $this->SetLineWidth(1);
    $this->Cell($w,9,$title,1,1,'C',1);
    $this->Ln(10);
    //Save ordinate
    $this->y0=$this->GetY();
}

function Footer()
{
    //Page
    $this->SetY(-15);
    $this->SetFont('Arial','I',8);
    $this->SetTextColor(128);
    $this->Cell(0,10,'PMDB v0.20 © Alan and Woland - Licensed under the GPL',0,0,'C');
    $this->Ln(4);
    $today = getdate();
    $mday = $today['mday']; $month = $today['month']; $year = $today['year'];
    $this->Cell(0,10,$mday.' '.$month.' '.$year.' - Created by FPDF 1.5.1',0,0,'C');
}
}

$pdf=new PDF();
$pdf->Open();
$pdf->AddPage();

$pdf->SetTextColor(0,0,0);
$pdf->SetFont('Arial','B',10);

$select_query="select id,title,year,imdb,genre,poster,rating,actor,disc,quality,`type`,resx,resy,notes,`release` from movies order by title";
$select_result=db_query($select_query,$dbcon);
$i=1;
while(list($id,$title,$year,$imdb,$genre,$poster,$rating,$actor,$disc,$quality,`$type`,$resx,$resy,$notes,`$release`)=mysql_fetch_array($select_result)){
    $pdf->Cell(40, 10, $i." - ".$title." (".$year.") - ". $genre. " (".$disc." CD)");
    $pdf->Ln(5);
    $i++;
}
$pdf->Output();
?>
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.
Invision Power Board © 2001-2008 Invision Power Services, Inc.