14 Replies - 3625 Views - Last Post: 23 April 2005 - 01:47 PM Rate Topic: -----

#1 Adsa  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 40
  • Joined: 21-April 05

php/mysql SELECT help

Posted 21 April 2005 - 09:59 PM

I am an absolute beginner at PHP and MySql so please be kind.

My problem is I am trying to pass on some information using a form and use this information to isolate the details required using the SELECT command. I know that I am getting the information from my form but am unsure on how to retrieve the necessary data from the database.

$sql = 'SELECT * '
        . ' FROM `clan_name` '
        . ' WHERE 1 AND `clan_name` '
        . ' LIKE \'Dr\' LIMIT 0, 30'; 


Where I have "Dr" i wish to replace this with the selected information from my form.

Help please.

Thanks

Is This A Good Question/Topic? 0
  • +

Replies To: php/mysql SELECT help

#2 DanceInstructor  Icon User is offline

  • New D.I.C Head
  • member icon

Reputation: 0
  • View blog
  • Posts: 41
  • Joined: 18-March 05

Re: php/mysql SELECT help

Posted 21 April 2005 - 11:16 PM

Hmm lets say in your form you have a select box with clan names, something like:

<select name="clan_name">
<option value="Agent">Agents
<option value="Fear">Fear This
</select>



Then in your script you might do this:

$clan_name = $_POST['clan_name'];
$sql = "SELECT *
        FROM `clan_name`
        WHERE 1 AND `clan_name`
        LIKE $clan_name LIMIT 0, 30";



Note the use of ". You must use double quotes when using variables in strings, if you use single quotes php assumes its all text and does not look for variables.
Was This Post Helpful? 0
  • +
  • -

#3 cyberscribe  Icon User is offline

  • humble.genius
  • member icon

Reputation: 10
  • View blog
  • Posts: 1,062
  • Joined: 05-May 02

Re: php/mysql SELECT help

Posted 21 April 2005 - 11:25 PM

ditch the
1 AND


...it's useless. :)
Was This Post Helpful? 0
  • +
  • -

#4 Adsa  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 40
  • Joined: 21-April 05

Re: php/mysql SELECT help

Posted 21 April 2005 - 11:52 PM

Thanks for that, but for some reason I get this now

Unknown column 'Dr' in 'where clause'


At least now I am getting the variable into the statment. :)
Was This Post Helpful? 0
  • +
  • -

#5 DanceInstructor  Icon User is offline

  • New D.I.C Head
  • member icon

Reputation: 0
  • View blog
  • Posts: 41
  • Joined: 18-March 05

Re: php/mysql SELECT help

Posted 22 April 2005 - 12:28 AM

Post the code you used please. We may need a peek at your form code as well, not sure. Lets also clarify the structure of your database. It sounds like the sql statement has a value in the wrong place though.
Was This Post Helpful? 0
  • +
  • -

#6 Adsa  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 40
  • Joined: 21-April 05

Re: php/mysql SELECT help

Posted 22 April 2005 - 03:02 AM

Here is the code I used for MySql minus the mysql_connect stuff :)

<?php
$clan_select = $_POST['clan_name'];
$sql = "SELECT *
       FROM `clan_name`
       WHERE `clan_name`
       = $clan_select";
while ($newArray = mysql_fetch_array($result)) {
	$id  = $newArray['id'];
	$clan_name = $newArray['clan_name'];
                $challenging_clan = $newArray['challenging_clan'];
	echo "$clan_name<br>"
                echo "$challenging_clan <br>";
}
?>



and here is an extract of the code I used for the form.

<p><strong>Select a clan</strong></p>
<select name="clan_name" >
<option value="Dr" >Dr</option>
<option value="Blackwidow" >Blackwidow</option>
<option value="Redback" >Redback</option>
<option value="THE" >THE</option>
</select>
<p><input type="submit" value="send"></p>


and finally here is the structure of the database.

id   int(40) 
clan_name   varchar(100)
challenging_clan   varchar(100 )
challenge_score   int(40)
defender_score   int(40)  
date   datetime No  0000-00-00 00:00:00  
map   varchar(100)

Was This Post Helpful? 0
  • +
  • -

#7 Amadeus  Icon User is offline

  • g+ + -o drink whiskey.cpp
  • member icon

Reputation: 248
  • View blog
  • Posts: 13,506
  • Joined: 12-July 02

Re: php/mysql SELECT help

Posted 22 April 2005 - 06:18 AM

try
$sql = "SELECT *
      FROM `clan_name`
      WHERE `clan_name`
      = '".$clan_select."'";


Please note that after the equal sign, there is a single quote, then double quotes. Similarly, after the final concatenator, there is a set of double quotes, a single quote, and then double quotes again.
Was This Post Helpful? 0
  • +
  • -

#8 DanceInstructor  Icon User is offline

  • New D.I.C Head
  • member icon

Reputation: 0
  • View blog
  • Posts: 41
  • Joined: 18-March 05

Re: php/mysql SELECT help

Posted 22 April 2005 - 01:38 PM

Actually he shouldn't have to concatenate the string. That is the whole point of using double quotes in php, so you can insert variables straight into a string without extra syntax. So this should work: (but then again you never know :P )

$sql = "SELECT * FROM `clan_name` WHERE `clan_name` = $clan_select";


My next question is: You gave us the field names of a table in your database. What is the table name? If it is the same as a field in the table I suggest you change it to something else to eliminate possible confusion. Now lets look at the sql statement again.

$sql = "SELECT * FROM `clan_name` WHERE `clan_name` = $clan_select";


means (this is not code, just for explanation):

$sql = "SELECT allvalues FROM tablename WHERE fieldname isexactlythesameas $clan_select";

So you might wanna go back to using LIKE in case the value in the database is not exactly the same as the value assigned in the form. Also lets make sure about the table name.

PS READ THIS:
I just noticed that I don't see where you actually made your query in the code you supplied. Do you actually have:

$result = mysql_query($sql);


in your code? Because it definitly won't work if you don't execute the query.

This post has been edited by DanceInstructor: 22 April 2005 - 01:45 PM

Was This Post Helpful? 0
  • +
  • -

#9 Amadeus  Icon User is offline

  • g+ + -o drink whiskey.cpp
  • member icon

Reputation: 248
  • View blog
  • Posts: 13,506
  • Joined: 12-July 02

Re: php/mysql SELECT help

Posted 22 April 2005 - 01:42 PM

Yeah, the only reason I suggested concatenating the strings was due to his error message. It seems clear that 'Dr' is being interpreted as a column name, not as a string variable.

Quote

PS READ THIS:
I just noticed that I don't see where you actually made your query in the code you supplied. Do you actually have:

CODE
$result = mysql_query($sql);


in your code? Because it definitly won't work if you don't execute the query.

Good spot, never even noticed myself... :)
Was This Post Helpful? 0
  • +
  • -

#10 Adsa  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 40
  • Joined: 21-April 05

Re: php/mysql SELECT help

Posted 22 April 2005 - 04:09 PM

Amadeus, on Apr 22 2005, 06:18 AM, said:

try
$sql = "SELECT *
      FROM `clan_name`
      WHERE `clan_name`
      = '".$clan_select."'";


Please note that after the equal sign, there is a single quote, then double quotes. Similarly, after the final concatenator, there is a set of double quotes, a single quote, and then double quotes again.


Thank you very much that works a treat. :D

DanceInstructor, on Apr 22 2005, 01:38pm, said:

PS READ THIS:
I just noticed that I don't see where you actually made your query in the code you supplied. Do you actually have:


  
$result = mysql_query($sql); 




in your code? Because it definitly won't work if you don't execute the query.


Ops My mistake, in copying and pasting I accidently left it out.

Thanks Amadeus and DanceInstructor for your help. :D

This post has been edited by Adsa: 22 April 2005 - 04:10 PM

Was This Post Helpful? 0
  • +
  • -

#11 skyhawk133  Icon User is offline

  • Head DIC Head
  • member icon

Reputation: 1865
  • View blog
  • Posts: 20,278
  • Joined: 17-March 01

Re: php/mysql SELECT help

Posted 22 April 2005 - 04:10 PM

Thanks guys for helping out, and welcome to dream.in.code Adsa!
Was This Post Helpful? 0
  • +
  • -

#12 cyberscribe  Icon User is offline

  • humble.genius
  • member icon

Reputation: 10
  • View blog
  • Posts: 1,062
  • Joined: 05-May 02

Re: php/mysql SELECT help

Posted 22 April 2005 - 08:41 PM

Adsa, on Apr 22 2005, 04:09 PM, said:

Thanks Amadeus and DanceInstructor for your help. :D

And me. I optimized your query by 1/100000ms at least. :)
Was This Post Helpful? 0
  • +
  • -

#13 Amadeus  Icon User is offline

  • g+ + -o drink whiskey.cpp
  • member icon

Reputation: 248
  • View blog
  • Posts: 13,506
  • Joined: 12-July 02

Re: php/mysql SELECT help

Posted 23 April 2005 - 12:54 AM

Adsa, on Apr 22 2005, 06:09 PM, said:

Amadeus, on Apr 22 2005, 06:18 AM, said:

try
$sql = "SELECT *
      FROM `clan_name`
      WHERE `clan_name`
      = '".$clan_select."'";


Please note that after the equal sign, there is a single quote, then double quotes. Similarly, after the final concatenator, there is a set of double quotes, a single quote, and then double quotes again.


Thank you very much that works a treat. :D

No problem, sir....glad to have helped....have a great night.
Was This Post Helpful? 0
  • +
  • -

#14 Amadeus  Icon User is offline

  • g+ + -o drink whiskey.cpp
  • member icon

Reputation: 248
  • View blog
  • Posts: 13,506
  • Joined: 12-July 02

Re: php/mysql SELECT help

Posted 23 April 2005 - 09:58 AM

DanceInstructor, on Apr 22 2005, 03:38 PM, said:

Actually he shouldn't have to concatenate the string. That is the whole point of using double quotes in php, so you can insert variables straight into a string without extra syntax. So this should work: (but then again you never know :P )

$sql = "SELECT * FROM `clan_name` WHERE `clan_name` = $clan_select";

I realized I did not really explain the problem...it was ocurring due to what is expected in the form of sql synyax, not php. While php translates the variable into it's value quite efficiently, sql syntax also has to be taken into consideration. When searching for string matches, sql requires that the string to be searched for be encased in quotes. Had the variable held 'Dr', with the quotes, it would have been fine, but since it held only Dr without the quotes, they had to be added.
Was This Post Helpful? 0
  • +
  • -

#15 DanceInstructor  Icon User is offline

  • New D.I.C Head
  • member icon

Reputation: 0
  • View blog
  • Posts: 41
  • Joined: 18-March 05

Re: php/mysql SELECT help

Posted 23 April 2005 - 01:47 PM

Actually I made a mistake when I posted that statement. This is what I should have posted:

$sql = "SELECT * FROM `clan_name` WHERE `clan_name` = '$clan_select'";

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1