Select from database and change to id?

[askeli]

Hi

I am having a bit of trouble here trying to go to a name in a Mysql database and retrieve its id, what i am actually trying to do is:

convert a url with for example dir/index.php?action=showcat&idcat=4

to

dir/tablename/ instead of dir/4/ (4 being a table id)

so what i need is to change the name in the url back to the id, i have tried the following without luck.

if ($catid){
$db=mysql_connect("localhost","user","password");
mysql_select_db("database",$db);
$query="SELECT 'id' FROM dir_categories WHERE name= '.$catid.' ";
$result=mysql_query($query);
$record=mysql_fetch_assoc($result);
$idcat=$record["id"];
}

i have setup the rewrite successfully in htaccess
(thanks to this website) to rewrite as "catid"

I am just trying to use the table names instead of id's for SE purposes, as /widgetgreen/ is better than /4/
Basically is what im trying possible? or am i wasting my time trying

I hope somebody understands what im on about as i seem to of confused myself.

Many Thanks

[jatar_k]

are you assigning $_GET['catid'] to $catid?

what happens if you echo $query?

[askeli]

to be honest i dont know.

im very new to MySql and just try to pick up what i can.

as simple as i can:

i have a dir/index.php?action=showcat&idcat=NUMBER url

the number is the "id" in the database table, i have converted the url with rewrite to dir/NUMBER but each "catergory" has a name which i would like to use instead of the number i.e. widgets white has id 1, widgets green has id 2 and so on. so what im trying to do is call dir/index.php?action=showcat&idcat=NUMBER with dir/widgets_white/

i tried using "catid" in url which would find the name and return the "id" as "idcat" with

if ($catid){
$db=mysql_connect("localhost","user","password");
mysql_select_db("database",$db);
$query="SELECT 'id' FROM dir_categories WHERE name= '.$catid.' ";
$result=mysql_query($query);
$record=mysql_fetch_assoc($result);
$idcat=$record["id"];
}

oh dear i think ive confused myself again :(

Thank you very much

[jatar_k]

try this and see what you get

if ($catid){
$db=mysql_connect("localhost","user","password");
mysql_select_db("database",$db);
$query="SELECT 'id' FROM dir_categories WHERE name= '.$catid.' ";
echo $query;
die();
$result=mysql_query($query);
$record=mysql_fetch_assoc($result);
$idcat=$record["id"];
}

that will show your actual query, something like
SELECT 'id' FROM dir_categories WHERE name= 'widgetgreen'

which, if run, should return 4

if you get this
SELECT 'id' FROM dir_categories WHERE name= ''

then the value is not getting assigned properly and will help us start narrowing down the issue.

Aslo, are you getting any errors?

[askeli]

thanks for that, your right this is what i get:

SELECT 'id' FROM dir_categories WHERE name= '.widgetgreen.'

No errors

Thanks

[jatar_k]

aha

look at the dots in the catname

$query="SELECT 'id' FROM dir_categories WHERE name= '.$catid.' ";

should be

$query="SELECT 'id' FROM dir_categories WHERE name= '$catid' ";

[askeli]

changed it, now it shows:

SELECT 'id' FROM dir_categories WHERE name= 'widgetgreen'

that didnt seem to fix it :(

[jatar_k]

did you get rid of the

echo $query;
die();

and then run it?

[askeli]

i didnt, have now and it still doesnt work.

i have just been playing with PHPMyAdmin, i dont know if thats right but i ran the command created with PHPMyAdmin:

SELECT id FROM `dir_categories` WHERE name = 'widgetgreen'

and it showed 4

i chose "create php" this is what i got

$sql = 'SELECT id';
$sql .= 'FROM `dir_categories` ';
$sql .= 'WHERE name = \'widgetgreen\' LIMIT 0, 30';

and executed it showed 4 again

probably means nothing but im learning

[jatar_k]

ok that's good then we know the query is being created properly now. Using echo to show the constructed query is a good debugging method, as is running the created query through phpmyadmin or the command line.

Now onto the next step, let's see if mysql will throw an error.

if ($catid){
$db=mysql_connect("localhost","user","password");
mysql_select_db("database",$db);
$query="SELECT 'id' FROM dir_categories WHERE name= '.$catid.' ";
echo '<p>',$query;
//die();
$result=mysql_query($query) or die(mysql_error());
$record=mysql_fetch_assoc($result);
echo "<pre>";
print_r($record);
echo "</pre>";
$idcat=$record["id"];
echo '<p>',$idcat;
}

so we have now added a couple more echo's to see what is happening with all of our variables. Let's see what happens with that.

[askeli]

this is what i got:

SELECT 'id' FROM dir_categories WHERE name= '.widgetgreen.'

(at the very top of the page)

before i carry on im putting this at the top of the index.php page, i dont know if it matters.

Thanks for all your help

[Timotheos]

You're regressing (jatar_k's fault ;-). Take the periods out of the select statement.

if ($catid){
$db=mysql_connect("localhost","user","password");
mysql_select_db("database",$db);
$query="SELECT 'id' FROM dir_categories WHERE name= '$catid'";
echo '<p>',$query;
//die();
$result=mysql_query($query) or die(mysql_error());
$record=mysql_fetch_assoc($result);
echo "<pre>";
print_r($record);
echo "</pre>";
$idcat=$record["id"];
echo '<p>',$idcat;
}

[askeli]

This is what i get with latest change:

SELECT 'id' FROM dir_categories WHERE name= 'widgetsgreen'

Array
(
[id] => id
)

id

[Timotheos]

Hehe we'll get there yet. Take out the single quotes around id.

$query="SELECT id FROM dir_categories WHERE name= '$catid'";

[Timotheos]

Add a loop to see all the results

if ($catid){
$db=mysql_connect("localhost","user","password");
mysql_select_db("database",$db);
$query="SELECT id FROM dir_categories WHERE name= '$catid'";
echo '<p>',$query;
$result=mysql_query($query) or die(mysql_error());
while ($record=mysql_fetch_assoc($result)) {
echo "<pre>";
print_r($record);
echo "</pre>";
$idcat=$record["id"];
echo '<p>',$idcat;
}
}

[askeli]

This is what i get with latest change:
SELECT id FROM dir_categories WHERE name= 'widgetsgreen'

Array
(
[id] => 4
)

4

looking good :)

[jatar_k]

thx Timotheos, bad paste ;)

[askeli]

how do i change this to see if it works? it displays the above but the page still doesnt work

[jatar_k]

comment out the echo statements

if ($catid){
$db=mysql_connect("localhost","user","password");
mysql_select_db("database",$db);
$query="SELECT id FROM dir_categories WHERE name= '$catid'";
//echo '<p>',$query;
$result=mysql_query($query) or die(mysql_error());
while ($record=mysql_fetch_assoc($result)) {
//echo "<pre>";
//print_r($record);
//echo "</pre>";
$idcat=$record["id"];
//echo '<p>',$idcat;
}
}

[askeli]

parse error now at bottom of page.

Thats it for tonight! thanks for all your help

jatar_k you have a sticky

[jatar_k]

>> parse error now at bottom of page.

what is the exact error? what line and what is on that line?

[askeli]

my mistake parse error gone.

Now it is fetching the "id" but not doing anything with it? if i echo idcat it shows 4 but the page stays on the index.php

[jatar_k]

time to look farther down in the code then.

Where is the spot that the id we grabbed is first used?
Does it do anything?

[askeli]

seems to be here:

$idcat=$_GET['idcat'];
if (empty($idcat) or!isset($idcat) or!is_numeric($idcat)) { $idcat=0; print build_home(); }
else {
$page=$_GET['page']

[jatar_k]

weren't you using catid in the url and then doing the code we worked out to grab the $idcat?

what happens if you comment out this line?

$idcat=$_GET['idcat'];

[askeli]

IT WORKS!

but :(

It doesnt work if the "name" has a slash in it.

eg widgetgreen/large/

if i enter widgetgreen/large/ in the address bar it brings up widgetgreen page

[jatar_k]

where does the slash come from?
what is the slash in the db?

you could use str_replace or some equivalent before you select it from the db

[askeli]

in the dbase widgetgreen is a "name" with a unique "id"
widgetgreen/large is a "name" with a unique "id"

but widgetgreen is also "short_name"
and large is also "short_name"

widgetgreen is a catergory
large is a sub-catergory of widgetgreen

Thanks for all your help, ill check back later dont have much time now.

[jatar_k]

what do you get in $catid when it is 'widgetgreen/large' in the url?

do you get widgetgreen/large
or widgetgreen

we can go from there. If it is just widgetgreen then it may be the rewrite that is nuking it.

[askeli]

i get "id" 4 which is widgetgreen not widgetgreen/large which is "id" 6

it seems to ignore anything after the slash

also "names" in the dbase with spaces are underscored
eg "name" widget_with_spots if that helps

Thank you