My sql select statement?

PrettyNormlGirl

What is wrong with this statement?

I have created 11 tables using phpMyAdmin on my webserver.

Then I used PHP Generator for MySql to generate the code scripts.

I then thought that I would be able to just slap the code onto the website and start capturing data. It worked ok until I realized I needed some extra fields here and there within the tables.

I created the new fields on the tables. and then recreated the code scripts. I now get this error on the administration side of my website:
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 'AS lp_AfslagID, t1.BestelNo, t1.UitgewerID, lp7. AS lp_UitgewerID, t1.IS' at line 1

Here is my sql_select statement:

function sql_select()
{
  global $conn;
  global $order;
  global $ordtype;
  global $filter;
  global $filterfield;
  global $wholeonly;

  $filterstr = sqlstr($filter);
  if (!$wholeonly && isset($wholeonly) && $filterstr!='') $filterstr = "%" .$filterstr ."%";
  $sql = "SELECT * FROM (SELECT t1.`BoekID`, t1.`Titel`, t1.`Skrywer`, t1.`VerkoopPrys`, t1.`AankoopPrys`, t1.`AfslagID`, lp5. AS `lp_AfslagID`, t1.`BestelNo`, t1.`UitgewerID`, lp7. AS `lp_UitgewerID`, t1.ISBN, t1.`Kopiereg_Datum`, t1.`Uitgawe`, t1.`KategorieID`, lp11. AS `lp_KategorieID`, t1.`TaalID`, lp12. AS `lp_TaalID`, t1.`Vertaler`, t1.`BuitebladID`, lp14. AS `lp_BuitebladID`, t1.`Bladsye`, t1.`KondisieID`, lp16. AS `lp_KondisieID`, t1.`Gepubliseer`, t1.`Voorraad`, t1.`Voorraad_Loc`, t1.`BoekVoorblad`, t1.`BoekBeskrywing` FROM `boeke` AS t1 LEFT OUTER JOIN  AS lp5 ON (t1.`AfslagID` = lp5.) LEFT OUTER JOIN  AS lp7 ON (t1.`UitgewerID` = lp7.) LEFT OUTER JOIN  AS lp11 ON (t1.`KategorieID` = lp11.) LEFT OUTER JOIN  AS lp12 ON (t1.`TaalID` = lp12.) LEFT OUTER JOIN  AS lp14 ON (t1.`BuitebladID` = lp14.) LEFT OUTER JOIN  AS lp16 ON (t1.`KondisieID` = lp16.)) subq";
  if (isset($filterstr) && $filterstr!='' && isset($filterfield) && $filterfield!='') {
    $sql .= " where " .sqlstr($filterfield) ." like '" .$filterstr ."'";
  } elseif (isset($filterstr) && $filterstr!='') {
    $sql .= " where (`BoekID` like '" .$filterstr ."') or (`Titel` like '" .$filterstr ."') or (`Skrywer` like '" .$filterstr ."') or (`VerkoopPrys` like '" .$filterstr ."') or (`AankoopPrys` like '" .$filterstr ."') or (`lp_AfslagID` like '" .$filterstr ."') or (`BestelNo` like '" .$filterstr ."') or (`lp_UitgewerID` like '" .$filterstr ."') or (ISBN like '" .$filterstr ."') or (`Kopiereg_Datum` like '" .$filterstr ."') or (`Uitgawe` like '" .$filterstr ."') or (`lp_KategorieID` like '" .$filterstr ."') or (`lp_TaalID` like '" .$filterstr ."') or (`Vertaler` like '" .$filterstr ."') or (`lp_BuitebladID` like '" .$filterstr ."') or (`Bladsye` like '" .$filterstr ."') or (`lp_KondisieID` like '" .$filterstr ."') or (`Gepubliseer` like '" .$filterstr ."') or (`Voorraad` like '" .$filterstr ."') or (`Voorraad_Loc` like '" .$filterstr ."') or (`BoekVoorblad` like '" .$filterstr ."') or (`BoekBeskrywing` like '" .$filterstr ."')";
  }
  if (isset($order) && $order!='') $sql .= " order by `" .sqlstr($order) ."`";
  if (isset($ordtype) && $ordtype!='') $sql .= " " .sqlstr($ordtype);
  $res = mysql_query($sql, $conn) or die(mysql_error());
  return $res;
}

Bjom

So you got a nice error message.... have you looked at or "near" that position in the SQL?

Here ist what you will find (wrong)

....t1.`AfslagID`, [B][COLOR="Red"]lp5.[/COLOR][/B] AS `lp_AfslagID....

one period too many or rather a field name missing

BTW: It's almost always better to have your script print out the SQL and just look at that (i.e. echo $sql; ...).

regards

Bjom

PrettyNormlGirl

Bjom;10916246 wrote:
So you got a nice error message.... have you looked at or "near" that position in the SQL?

Here ist what you will find (wrong)
....t1.`AfslagID`, [B][COLOR="Red"]lp5.[/COLOR][/B] AS `lp_AfslagID....
one period too many or rather a field name missing

BTW: It's almost always better to have your script print out the SQL and just look at that (i.e. echo $sql; ...).

regards

Bjom

Hi Bjom
Thanks for replying.

I forgot to mention that I really don't know SQL at all, thus, where do I type the echo $sql to get the script to print out the SQL?

I can further say that the AfslagID field goes to a lookup table that contains 2 fields. But maybe you can see that from the code?
It seems that everytime the code needs to go to a lookup table, it uses lp5 or lp"something".

PrettyNormlGirl

I have now rerun the generator and I can see that some fields just aren't getting included.

Can anybody suggest a really good generator that is for free? Like I said before, I am using PHP Generator for MySQL.

Bjom

THE best generator I know of is MS Access. If you can import your tables there, then you can play around a lot and get a feel for SQL.

However: for simple select statements like yours, it helps a lot to just type them out or write them on a piece of paper and then build your code yourself.

the "print out" part of my first reply would go into your PHP code. if you simply add this line:

echo $sql;

It will send the SQL that your code generated to the screen and you can inspect it in its full splendor, without the PHP interfering with your reading experience.

When you find your SQL string is as you want it to be just slap // in front of it to comment the line out like so:

// echo $sql;

regards

Bjom

Roger_Ramjet

PrettyNormlGirl;10916476 wrote:
Can anybody suggest a really good generator that is for free? Like I said before, I am using PHP Generator for MySQL.

Well the best generator that I know of is POG which will generate everything for you including the php class with CRUD sql and the create table sql

PrettyNormlGirl

Thanks Bjom and Roger.

I can see I still have a lot to learn.

I spoke to a faraway friend today and he explained my error to me and I finally understood what you said Bjom, where the field must come and once I understood, I could only laugh.

I will let you know my progress.

Sxooter

And keep practicing on SQL. Basic, simple SQL is really pretty simple, and the concepts of grouping, ordering, and subselects can come later on.

Bjom

Oh, sorry if I was too cryptic! Just keep on asking next time I'm too terse in my statements.

Seems you're on the right track - a little practice and you'll get into it, I'm sure!

all the best

Bjom

PrettyNormlGirl

Roger Ramjet;10916623 wrote:
Well the best generator that I know of is POG which will generate everything for you including the php class with CRUD sql and the create table sql

Roger, I had a quick look at POG and I have 2 questions.

I see that I can add the fieldnames and then generate the code, but what I don't understand yet is how to ensure that the code takes into account a lookup table.
The other generator that I used took into account every table (or it looks like it does) that I generated and the relations between the tables, and with POG it seems that I have to now retype every field definition online, to get my generated code.

What am I missing here?