Plea for newbie mysql advice

[pixel_juice]

As mentioned in a previous thread, I'm getting started with mysql, and am very excited by the possiblites I might add.

I don't have much experiences with databases, so some of my questions will be very basic. Unfortunately, I find it very difficult to digest information in an abstract context - i.e. I learn very quickly working with real code, but I find the hundreds of tutorials out there somewhat difficult to apply to what I'm trying to do.

I have a database set up, and I've got the basics down like retrieving info from the database and writing to it. At the moment, I retrieve individual records from the database using this line of php:

$result = mysql_query("SELECT * FROM atable WHERE pagename=$pagename",$db);

Pagename is set as the primary key and unique id. This means that by going to widget.com/?pagename=1 etc. I can make a page based on the data in the table.

However, I don't want to call pages via a number, I want to do it via a descriptive page name. But if I change the values of the field to letters instead of numbers I get a "Supplied argument is not a valid MySQL result resource" error on the resulting page.

Presumably this is because I have to use numbers in this field - because of the SELECT FROM function? Or because I haven't understood primary keys and unique ids?

Any advice on what I can do to be able to use characters in the variable in the url rather than numbers. Something like page.php?pagename=contact-us

Many thanks for any help - and apologies that I clearly don't really know what I'm doing :)

[ikbenhet1]

Did you declare the field associated with $pagename as an integer or as varchar?
I hope it's set to integer, then the problem is easy, change the field to varchar or text.

I assume that you didn't use any special characters like " or ' because then you would need to escape that string with
mysql_escape_string();
ie: $pagename=mysql_escape_string($pagename);

and you can leave the $db out of the select like:

$result = mysql_query("SELECT * FROM atable WHERE pagename='".$pagename."'");

Hope this helps,
Cheers

[pixel_juice]

I did use a varchar rather than an integer. There aren't any special characters (a hyphen doesn't count I assume).

To create the table I used the command:

CREATE TABLE atable( pagename varchar(30) DEFAULT '0' NOT NULL, ... more fields etc. etc.

If I set pagename as a number, then call the page via page.php?pagename=1 it works fine. If I use characters I get the error mentioned above.

[jatar_k]

A couple of commands to use when trying to figure out what is up with a column or table.

DESCRIBE [mysql.com]
SHOW [mysql.com]

describe atable;
show create table atable;

These may give you a little extra data as to what is going on. I think I would try creating the table with no default or just changing the default to a string such as 'nopage' or some such and see if it might just be confused.

You can change tables with ALTER TABLE [mysql.com]

Is it the exact same lines of code either way except for the change from a number to a string? Do you have a line number or specific query that is misbehaving?

[ikbenhet1]

Can you try this:

$result = mysql_query("SELECT * FROM atable WHERE pagename='".$pagename."',$db);

The only thing different is the " around $pagename.
If it's not the sollution, ill but out from here.

[Timotheos]

For debugging you could also try hardcoding it in at first to see if it's just a db problem.
$result = mysql_query("SELECT * FROM atable WHERE pagename='mytestpage'");

BTW, if you're new then have you been introduced to phpMyAdmin for managing your database? It's a great tool.

[pixel_juice]

I really appreciate the help guys - even if I haven't got it working yet, you're still pushing me in the right directions. Feel free to tell me to RTFM if you like ;)

jatar_k - I'm actually using phpmyadmin so i've got a good picture of what the table looks like and all the attributes etc.

I've tried messing with the code and I get the same result.

page.php?pagename=n returns "Warning: Supplied argument is not a valid MySQL result resource"

page.php?pagename=1 causes no problems.

I'm thinking that the problem may be related to my use of $myrow and mysql_fetch_array in some way?

$myrow = mysql_fetch_array($result);

Is what I'm using. Might this be the cause of my problems?

[hakre]

hi pixeljuice,

this is a tip which will lead all this into a more clear direction, a function called mysql_error [php.net].

if you use it right after the query of your database and output it, you can post the error message here for a better analysis.

here is some code, too:

mysql_query([...]);
echo mysql_errno() . ": " . mysql_error() . "\n";

give it a try ;)

- hakre

[jatar_k]

nice hakre

I do it this way (just for options)

$sql = "SELECT * FROM atable WHERE pagename = 'mytestpage'";
$query = mysql_query($sql) or die (mysql_errno().": ".mysql_error()."<BR>");

[pixel_juice]

Timotheos, no joy with your suggestion unfortunately.

With regards phpmyadmin, I am using it (be pretty lost without it I think!)

>>lead all this into a more clear direction

Apologies for my directionlessness (which is hopefully a word). I tend to code above my level of understanding, which I can normally work my way through, but sql is all a bit new to me i'm afraid.

Following hakre's advice I got the following:

1054: Unknown column 'n' in 'where clause'

Which led me here [experts-exchange.com]

This suggests the same as ikbenhet1 is msg#5 (thanks again ikbenhet1) - for non-numeric strings, I need to enclose them with quotes " or '. I'd already tried double-quotes which didn't work, but single quotes did the trick.

Woohoo! Thank you all so much for your time and patience :)

[Timotheos]

I need to enclose them with quotes " or '

Glad you found the problem. Unfortunately with this font it's a little hard to distinguish between the " and ' in the code examples.

[grnidone]

One trick that I use with phpMyAdmin is to validate code for my scripts. It is sort of a hack, but it saves me having to remember all the little picky aspects of MySQL Letsee do I use single quotes or double?

I use notepad and type the command the way I think it needs to be. Then I go to the SQL interface in phpMyAdmin and paste it in the box. If I get a result, then I know the command is right. If not, then I can tweak from there.

Also, I always use phpMyAdmin for creating tables. While I do know how to create tables by hand, there are so many little mistakes in the 'Create table' command that I just find it easier to use phpMyAdmin.

PhpMyAdmin does have its downfalls though: I have not found its 'create php code' to be very satisfactory, and I am frustrated by using its interface. But for a cheat to quickly validate code, it's great.

[jatar_k]

sound advice grnidone,

I am the opposite, I never use phpmyadmin, always ssh/telnet just give me that and I am happy. I usually get my script to build my queries and echo them. Then I run them through mysql to see how it likes it.

I get the chills anytime a program wants to "create php code".