[resolved] using a URL (string) as a primary key

[countrybumpkin]

Hey all

I'm new to this php and MySQL stuff. My script needs to be able to find out what the HTTP_HOST is (easy) and then look that up in a table to find a corresponding Client ID (because clients will have multiple URLS to get to their sites).

I'm using

[code]('SELECT clientID FROM domains WHERE domain = '.$SERVER_["HTTP_HOST"]);[/code]

but, of course, the WHERE part has to be a unique identifier. For some reason I can't work out how to make fields that contain URLs into a primary key.

Any ideas? I'm sure this has been done before :S

[trq]

Sorry... youve lost me. What exactly are you trying to do?

[countrybumpkin]

Sorry! Concise language was never my strong point.

I'm building a CMS which will run many (small) sites from one database. The system needs to check what URL the user has landed at in order to display the correct information on the page.

Say, the user pulls up www.aclient.com/viewArticle.php?ID=3, my script should then put the www.aclient.com part into a query on the database in order to establish what the CLIENT ID is, and then go ahead and display the right information for that client.

So, I have a table in the database called "domains", which lists each DOMAIN against a CLIENTID, because each client could have a few domains that they want to point to the system.

Do you follow? :-S hope that made sense........ I'm rapidly losing the ability to think right!

Hope you can help

[fert]

use
$_GET[id]

[countrybumpkin]

Sorry, I'm not sure what you mean....

where do I use that? In the query? How does that lookup the domain name in the database?

Sorry if I'm being stupid, total newbie in the world of PHP...

[trq]

Whats wrong with the query you initially posted? It does exactly what you want unless Im misunderstanding something.

[bqallover]

Perhaps you need to wrap your url in quotes?  Apart from that, it looks fine.

[code]
("SELECT clientID FROM domains WHERE domain = '$SERVER_[HTTP_HOST]'");
[/code]

[trq]

Well, if were going to get into specifics its $_SERVER['HTTP_HOST'] aswell. I thought this was just a simple example.

[code=php:0]
$sql = "SELECT clientID FROM domains WHERE domain = '{$_SERVER['HTTP_HOST']}';";
[/code]

[fert]

[quote]
Say, the user pulls up www.aclient.com/viewArticle.php?ID=3, my script should then put the www.aclient.com part into a query on the database in order to establish what the CLIENT ID is, and then go ahead and display the right information for that client.
[/quote]
What i'm getting is that you want the page to display differently based on what ID is and to do that you use $_GET[id] and if ithe URL is www.mysite.com/view.php?id=3 then $_GET[id] would be equal three.

I'm sorry if this answers your question, but i'm not sure exactly what you're asking.

[countrybumpkin]

Ok, that sounds good. I'll try what you suggest!

I think my problem is on the mySQL side. Doesn't the WHERE part of the statement have to be a unique/primary key field? Or am I just dreaming that...

Oh, and in my example, the ID=3 is the ID of the article to display, which could be a sydicated piece of content across multiple clients. I am re-branding content based on the URL. So the ID=3 is the article number, not the client number, or else GET would have been a very good idea!

Cheers for all your suggestions.

Ben

[trq]

[quote]Doesn't the WHERE part of the statement have to be a unique/primary key field?[/quote]

Nope. It will just return multiple records if they exist and satisfy the WHERE clause.

[fert]

[quote author=countrybumpkin link=topic=114077.msg464008#msg464008 date=1162861681]
Oh, and in my example, the ID=3 is the ID of the article to display, which could be a sydicated piece of content across multiple clients. I am re-branding content based on the URL. So the ID=3 is the article number, not the client number, or else GET would have been a very good idea!
[/quote]
It could be used as an article number.

[countrybumpkin]

Right - have tried your suggestions, but sadly to no avail. The error I keep getting is:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /content/.........../includes/DbConnector.php on line 50

Here's the offending code:

[code]//*** Function: fetchArray, Purpose: Get array of query results ***
function fetchArray($result) {
return mysql_fetch_array($result);
}[/code]

[trq]

That isn't the offending code. More than likely your query is failing and therefore $result is not a mysql resource. Post the code around your call to mysql_query(), and make sure your checking it actually worked before trying to use it. eg;

[code=php:0]
if ($result = mysql_query($sql)) {
  // $result holds a connection resource.
} else {
  // Something failed.
  echo mysql_error();
}
[/code]

[countrybumpkin]

Ah ha!

Thats brilliant, you were right. And I had mistyped something somewhere along the line, hence why it was failing. Problem solved, everything works a treat now!

Thanks for all your help and for being so patient!!

[trq]

No worries. I'll mark the thread resolved.