Jump to content


Photo

PHP MySQL Query Issue


  • Please log in to reply
15 replies to this topic

#1 zebe

zebe
  • Members
  • PipPip
  • Member
  • 24 posts

Posted 01 August 2006 - 06:27 PM

Hi,

I'm trying to run a query like the following example:

$activity = $HTTP_POST_VARS["activity"];
$sql = "SELECT first, last, email FROM student_involvement WHERE " . $activity . " = 1"; 
$result = mysql_query($sql) or die(mysql_error()); 

The variable $activity is pulling a column name from a predefined select list of activities. Each one of these fields is an int field and if it is true it will have a 1 false a 0. The query fails:

SELECT first, last, email FROM student_involvement WHERE acapella = 1
EXPORT ERROR:Unknown column 'acapella' in 'where clause'

Does anyone know why this is failing? The columns exist, I can pull general data from that table, but it seems to throw errors when I try to implement a WHERE clause...

Thanks for all of your help in advance!

#2 HeyRay2

HeyRay2
  • Members
  • PipPipPip
  • Advanced Member
  • 223 posts

Posted 01 August 2006 - 06:48 PM

Does hardcoding the column name work?

$sql = "SELECT first, last, email FROM student_involvement WHERE acapella = 1"; 

Let us know if the query is successful.

#3 zebe

zebe
  • Members
  • PipPip
  • Member
  • 24 posts

Posted 01 August 2006 - 06:50 PM

Just hardcoded it as requested and that is throwing the same error as well... Not sure what's up. The table exists and the columns do as well...

#4 Caesar

Caesar
  • Members
  • PipPipPip
  • Advanced Member
  • 1,025 posts

Posted 01 August 2006 - 06:51 PM

Yeah...paste this before your query & let us know if it works:

<?php

$query = mysql_query("SELECT * FROM student_involvement WHERE acapella ='1'");

$res = mysql_fetch_array($query);

print_r($res);exit;

?>

PHP Ninja

#5 zebe

zebe
  • Members
  • PipPip
  • Member
  • 24 posts

Posted 01 August 2006 - 06:57 PM

I get a blank page...

#6 Caesar

Caesar
  • Members
  • PipPipPip
  • Advanced Member
  • 1,025 posts

Posted 01 August 2006 - 07:07 PM

Then the array is blank. It's not retrieving anything from that field in the table. Mind pasting a screenshot of that table?
PHP Ninja

#7 zebe

zebe
  • Members
  • PipPip
  • Member
  • 24 posts

Posted 01 August 2006 - 07:16 PM

Screenshot is attached

[attachment deleted by admin]

#8 Caesar

Caesar
  • Members
  • PipPipPip
  • Advanced Member
  • 1,025 posts

Posted 01 August 2006 - 07:24 PM

Though the field/column is there....is there any data in it yet?
PHP Ninja

#9 zebe

zebe
  • Members
  • PipPip
  • Member
  • 24 posts

Posted 01 August 2006 - 07:26 PM

Yes,

I have a form that was placing data into the fields. If a user checked that activity it would store a 1 in the respective field, 0 otherwise. All the int fields are storing are basic binary true false data from checkboxes. When I browse, those fields have 0's and 1's in them, so there is data in there...

#10 king arthur

king arthur
  • Members
  • PipPipPip
  • Advanced Member
  • 335 posts
  • LocationUK HQ

Posted 01 August 2006 - 07:27 PM

This table is definitely named "student_involvement"?
Sir Isaac Newton said "If I have seen farther, it is by standing on the shoulders of giants". But it is not recorded as to whether he said it before or after he was hit on the head by a falling apple.

#11 zebe

zebe
  • Members
  • PipPip
  • Member
  • 24 posts

Posted 01 August 2006 - 07:30 PM

Yes it is called student_involvement.

I can extract first name last name etc fine, but the problem arises when I try to do a where clause on the integer fields...

#12 Caesar

Caesar
  • Members
  • PipPipPip
  • Advanced Member
  • 1,025 posts

Posted 01 August 2006 - 07:34 PM

If you convert the field to varchar, and then run the script again...what are the results? I asume if you add the WHERE clause criteria using a field that is an integer but also set as a primary key, it won't return the error?
PHP Ninja

#13 zebe

zebe
  • Members
  • PipPip
  • Member
  • 24 posts

Posted 01 August 2006 - 07:38 PM

Changed the `acapella` column to a varchar(1) type, ran it again and got the same unknown column in where clause error...

#14 Caesar

Caesar
  • Members
  • PipPipPip
  • Advanced Member
  • 1,025 posts

Posted 01 August 2006 - 07:49 PM

If you would like (Because I'm very damned curious :-P) send me an email and I will give you my AIM sn & I can help troubleshoot this for you.  :) (sammyone@gmail.com)
PHP Ninja

#15 ryanlwh

ryanlwh
  • Staff Alumni
  • Advanced Member
  • 511 posts

Posted 01 August 2006 - 08:03 PM

are you connecting to a different database from php that has this table but without the acapella column?
Please use EDIT * 100...
Please use
or [php] * 1000...

PLEASE READ THE POSTED SOLUTIONS CAREFULLY * 1000000...

#16 Caesar

Caesar
  • Members
  • PipPipPip
  • Advanced Member
  • 1,025 posts

Posted 01 August 2006 - 08:07 PM

are you connecting to a different database from php that has this table but without the acapella column?


True dat. Check to make sure you're not doing that.
PHP Ninja




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users