Link to home
Start Free TrialLog in
Avatar of tarakwar101
tarakwar101Flag for United States of America

asked on

wrong syntax, whats correct?

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)))
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

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

ASKER

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.
MySQL server version for the right syntax to use near ' and CompanyID =sml123' at line 1

if (!($result = mysql_query("SELECT * FROM STUDENTS WHERE IsAdmin='Y', and CompanyID ='".$session_data['CompanyID'] ."'" , $link)))
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)))

if (!($result = mysql_query("SELECT * FROM STUDENTS WHERE CompanyID ='".$session_data['CompanyID']."' and IsAdmin=Y", $link)))
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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?
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
changed pts.
Avatar of kamermans
kamermans

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;
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

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.
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.
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;
?
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.
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?
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
maybe a "limit 1" or something, not exactly sure how that works?
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
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);
 }
Since both of helped on this, I will split the pts. thanks