?
Solved

wrong syntax, whats correct?

Posted on 2006-05-04
23
Medium Priority
?
160 Views
Last Modified: 2013-12-12
I tried to few diff ways getting the Y to work   '\"Y\"'  Y", "Y",  keep getting an error.
If you could put it companyid first then IsAdmin, that would be better but when I tried I got same error.
if (!($result = mysql_query("SELECT * FROM STUDENTS WHERE IsAdmin=Y, and CompanyID =".$session_data['CompanyID'], $link)))
0
Comment
Question by:tarakwar101
  • 15
  • 4
  • 4
23 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16610117
if (!($result = mysql_query("SELECT * FROM STUDENTS WHERE IsAdmin='Y', and CompanyID =".$session_data['CompanyID'], $link)))
0
 

Author Comment

by:tarakwar101
ID: 16610120
I am trying to match the logged in student with the companies admin, who I then can send an email reciept to. But need to get that info from DB first.
0
 

Author Comment

by:tarakwar101
ID: 16610237
MySQL server version for the right syntax to use near ' and CompanyID =sml123' at line 1
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16610253

if (!($result = mysql_query("SELECT * FROM STUDENTS WHERE IsAdmin='Y', and CompanyID ='".$session_data['CompanyID'] ."'" , $link)))
0
 

Author Comment

by:tarakwar101
ID: 16610285
If I changed it this way iget this error:internal error 1054:Unknown column 'sml123' in 'where clause'

if (!($result = mysql_query("SELECT * FROM STUDENTS WHERE CompanyID =".$session_data['CompanyID']." and IsAdmin=Y", $link)))
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16610298

if (!($result = mysql_query("SELECT * FROM STUDENTS WHERE CompanyID ='".$session_data['CompanyID']."' and IsAdmin=Y", $link)))
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1400 total points
ID: 16610309
or rather:

if (!($result = mysql_query("SELECT * FROM STUDENTS WHERE CompanyID ='".$session_data['CompanyID']."' and IsAdmin='Y'" , $link)))

to explain:
you need single quotes around the string values inside the sql command, so it would look like this:
SELECT * FROM STUDENTS WHERE CompanyID ='sml123' and IsAdmin='Y'

0
 

Author Comment

by:tarakwar101
ID: 16610333
each company has a coordinator so I am trying to match the session data -logged in user-with his companies coordinator so I am selecting from database the sessiondata companyid, which is sml123 and I want it to look in the column IsAdmin....uhh that won't work I just figured out as I am writting this.
I need a way to get the IsAdmin column to match up with the sessiondata's companyid, sml123 to find the same companyid from table that the IsAdmin has the same companyid and the IsAdmin row that is "Y", so then I can pull in the companies admins email address. Does this make sence?
0
 

Author Comment

by:tarakwar101
ID: 16610386
currently it looks at the logged in users IsAdmin row and it is 'N' how do I match the users companyid with that of the IsAdmin for that Company? So I can get the admins email, this way anytime thwey make a purchase it will send an email to the admin of that company.
I can write the rest but having problem(Obviously) with getting the companies admin
0
 

Author Comment

by:tarakwar101
ID: 16610388
changed pts.
0
 
LVL 13

Expert Comment

by:kamermans
ID: 16610552
Can you post the table def for STUDENTS and if the admin's email is in another table post that table's def too.
Here's how:
DESCRIBE STUDENTS;
0
 

Author Comment

by:tarakwar101
ID: 16610650
Sorry I had wrong value for coordinator it should be IsCoord not IsAdmin
if (!($result = mysql_query("SELECT * FROM STUDENTS WHERE CompanyID ='".$session_data['CompanyID']."' and IsCoord='Y'" , $link)))
{
 DisplayErrMsg(sprintf("internal error %d:%s\n", mysql_errno(), mysql_error()));
 return 0;
 }
 $companyid = $row["CompanyID"];
$IsAdmin = $row["IsAdmin"];
 echo $IsAdmin;

The coordinator has a record in the students database just as a student does only his value for IsCoord=Y
These are the main Columns for students table:

studentid:
first name:
last name:
IsAdmin<-default is N
IsCoord <-default is N
CompanyID= NULL

0
 

Author Comment

by:tarakwar101
ID: 16610670
Doh just figured it out, I forgot to change the bottom isCorrd =  $row["IsCoord"];
 echo $IsCoord;
it now echos Y so I am assuming it will bring over that coords info, I am testing now.
0
 
LVL 13

Expert Comment

by:kamermans
ID: 16610679
That should work fine - I was typing an answer a min ago and realized that I had the same SQL as you :P - I think your query is fine.
0
 

Author Comment

by:tarakwar101
ID: 16611149
hmmm tried as a student and with a companyid and I get an error if i try echoing the $iscoord, before I was making a test purchase as me and I am the coord so it all worked and I got an email, but as soon as I logged in as a student with same companyid it didn't send email and got error when trying to echo $IsCoord;
?
0
 
LVL 13

Expert Comment

by:kamermans
ID: 16611163
This is a very simple piece of SQL - I think the problem is that your $session_data['CompanyID'] isn't getting set properly for the students.  Make you you set $session_data['CompanyID'] for the user when they login and maybe echo it on the page so you can confirm that it is correct.
0
 

Author Comment

by:tarakwar101
ID: 16611200
when i echo now is shows N wich is the users value? not the coordinators of the companies. hmmm
each studnet has a company and that companyid is unique so all students from that company say have sml123 as their companyid and one of the students from that company is the coordinator and in the IsCoord column for that student the value is Y, so why would it show N when I echo it?
0
 

Author Comment

by:tarakwar101
ID: 16611221
yes that is correct, the sessiondata companyid that echos fine, it is the IsCoord that comes up 'N' which is correct for that studnet but I am trying to get another students info that has the iscoord value of 'Y' to pull in. I need it to get the coords information to populate the email so when any student makes a purchase the coordinator gets an email
0
 

Author Comment

by:tarakwar101
ID: 16611225
maybe a "limit 1" or something, not exactly sure how that works?
0
 
LVL 13

Assisted Solution

by:kamermans
kamermans earned 600 total points
ID: 16611315
You need to run the query with sample data in phpMyAdmin or another SQL query browser and see what it returns:
SELECT * FROM STUDENTS WHERE CompanyID ='sml123' and IsCoord='Y';
0
 

Author Comment

by:tarakwar101
ID: 16617298
I tried that what I got returned was the logged on users iscoord=N which is all the users status IsCoord=N
I created 5 dummy accounts, all with the companyid as sml123 and I made myself as the coordinator IsCoord=Y
when I log on and make an order it sends me an email saying that studnetid=2 made an order which is me and I am the coordinator and I got the email that woked. But when I logged back on as a student that is not the coordinator and made an order it did nothing...no email was sent to the coordinator, I did an echo during a transaction and the echo produced IsCoord=N which is the students.

Students database table:

studentid:
first name:
last name:
IsAdmin<-default is N
IsCoord <-default is N
CompanyID= NULL

The coordinators records are inserted into the students database, just as the students records are, except the column IsCoord is changed to Y instead of default N
0
 

Author Comment

by:tarakwar101
ID: 16617443
Guess what is was? can't believe I fogot to add this line:
if (isset($session_data["CompanyID"]))
    {                                                                               
if (!($result = mysql_query("SELECT * FROM STUDENTS WHERE CompanyID ='".$session_data['CompanyID']."' and IsCoord='Y'" , $link)))
{
 DisplayErrMsg(sprintf("internal error %d:%s\n", mysql_errno(), mysql_error()));
 return 0;
 }
 $row = mysql_fetch_array($result); <---------HERE
$companyid = $row["CompanyID"];
$IsCoord = $row["IsCoord"];
$coordname =$row["Name"];
$coordemail =$row["EMail"];

 $date = date("F j, Y, g:i a");
 $smarty->assign("date",$date);
 }
0
 

Author Comment

by:tarakwar101
ID: 16617762
Since both of helped on this, I will split the pts. thanks
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article discusses four methods for overlaying images in a container on a web page
This article discusses how to implement server side field validation and display customized error messages to the client.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
Suggested Courses
Course of the Month15 days, 19 hours left to enroll

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question