PDA

View Full Version : Database access w/ CGI


Ken-
05-23-2000, 03:35 PM
Alright, if anyone has any clue about this, it would be a great help.

I know there are ways to do this, but I'm not sure how, and trying to find a script to do this has been difficult. There may be a mainstream script that does this that I passed up, who knows.

Is it possible to access a database via an indexing number or name, and display the data in an HTML template. For instance, the file would be called via a URL that looks like: whatever/get.cgi?ID=1 - The get program would then grab all the information associated with the "1" entry in the database and display different parts of it in different sections of say a table.

Rather than upload hundreds of different small HTML files, it would be a helluva lot easier if I were able to access the files from one data file.

Thanks again,
Ken

Ken-
05-23-2000, 03:38 PM
I should note that I've seen this done w/ Cold Fusion and ASP, but I don't want to go with an NT server.

Arn
05-23-2000, 06:19 PM
Hi Ken,

Yes - this is pretty straightforward to do with any scripting/db solution.

I'm not sure if you're looking for a pre-made sort of script... but if you have any programming experience, it's pretty easy to setup your self.

I personally use PHP/mySQL. PHP is a server side scripting language akin to ASP. I'm a big fan of PHP - it's very easy to use. mySQL is pretty easy to learn as well.

I'm not sure which hosting you're using now... but some hosts offer both with their standard monthly hosting. I've got a dedicated Linux server with both installed.

arn


------------------
http://www.weeno.com - Learn Stuff You Don't Know
Promote Your Site (http://www.weeno.com/Pages/promote.shtml) - write an article, get new/interested visitors.

Ken-
05-23-2000, 07:45 PM
Hi Ken,

Yes - this is pretty straightforward to do with any scripting/db solution.

I'm not sure if you're looking for a pre-made sort of script... but if you have any programming experience, it's pretty easy to setup your self.



Arn,

I figured it would be pretty straightforward, but unfortunately I don't have any scripting experience.

I did look at some PHP/mySQL information, and for a programming novice, it doesn't look all that simple to just pick up and code something like I need. Although I guess now is as good a time as ever to learn.

I'm going to keep looking though - thanks.

Ken

Arn
05-23-2000, 08:14 PM
Hi Ken,

Here's an example of a simple php script that will do what you ask.

If you call http://www.site.com/thisscript.php3?id=XX

it will look up item "XX" in your database, and pull the contents and print them to the page.

The key to PHP is that <? ?> offsets PHP - everything else is straight HTML.

Take a look:

--------------------------------
<?

/* SQL Database Setup */
$hostname = "sqlhost";
$dbusername = "username";
$dbpassword = "password";
$dbName = "database";

/* make connection to database */

MYSQL_CONNECT($hostname, $dbusername, $dbpassword) OR DIE("Unable to connect to database");
@mysql_select_db( "$dbName") or die( "Unable to select database");


/* Select the info you want from the database */

$query = "select html from mydatatable WHERE id=$id";
$result = MYSQL_QUERY($query);
$html = mysql_result($result,0,"html");


?>

<html>

<head>
<title>This is a My Page</title>
</head>

<body>

This is the Body of my HTML page. You can put static info here.

Here is the dynamic info to this page:

<? print $html ?>

<center>
Copyright 2000, Me
</center>

</body>

</html>
------------------------------------

This is a good intro article:
http://www.devshed.com/Server_Side/PHP/Introduction/



------------------
http://www.weeno.com - Learn Stuff You Don't Know
Promote Your Site (http://www.weeno.com/Pages/promote.shtml) - write an article, get new/interested visitors.

Arn
05-23-2000, 08:19 PM
Oh - one of the cool "features" of PHP3 is that any variable passed via the URL (id=123&whatever=hello) will automatically be populated when the script runs.

So, in the above script "$id" is already defined as the id=XX value that was passed to it.

arn


------------------
http://www.weeno.com - Learn Stuff You Don't Know
Promote Your Site (http://www.weeno.com/Pages/promote.shtml) - write an article, get new/interested visitors.

Ken-
05-23-2000, 10:07 PM
Arn,

I can't thank you enough. It works great.

If I wanted to grab multiple data entries, should I just use multiple queries?

For example,
-----
$query = "select blah from mydatatable WHERE id=$id";
$result = MYSQL_QUERY($query);
$blah = mysql_result($result,0,"blah");

$query = "select blah2 from mydatatable WHERE id=$id";
$result = MYSQL_QUERY($query);
$blah2 = mysql_result($result,0,"blah2");
----

I tried that, and it works fine. Is there a more efficient way to do it using just one query statement?

I think this is a great testimonial to how simple yet powerful database scripting is.

You saved me hours http://geekvillage.com/ubb/smile.gif Thank you again.

Ken

suresk
05-23-2000, 10:43 PM
Try something like this:


-------
$i = 0;
$query = "select blah from mydatatable WHERE id=$id";
$result = MYSQL_QUERY($query);
$number = MYSQL_NUM_ROWS($result);
while ($i < $number){
$query = "select blah from mydatatable WHERE id=$id";
$result = MYSQL_QUERY($query);
$blah = mysql_result($result,0,"blah");
$i++;
**


------------------
Spencer Uresk
spencer@uresk.net
http://www.uresk.net

Arn
05-23-2000, 10:45 PM
Great Ken! I didn't realize you had it all setup to go (php and mysql).

You can do it like this to get multiple:

Use a wildcard:

$query = "select * from mydatatable WHERE id=$id";
$result = MYSQL_QUERY($query);
$blah = mysql_result($result,0,"blah");
$blah2 = mysql_result($result,0,"blah2");
etc...

Or, you can specify:

$query = "select blah, blah2 from mydatatable WHERE id=$id";
$result = MYSQL_QUERY($query);
$blah = mysql_result($result,0,"blah");
$blah2 = mysql_result($result,0,"blah2");

Another thing... to select multiple rows... lets say you want to display all the "titles" from your table. (assuming you have a record called "title")

$query = "select title from mydatatable";
$result = MYSQL_QUERY($query);
$number = MYSQL_NUMROWS($result);

$i=0;

while ($i<$number)
{
$title = mysql_result($result,$i,"title");
print "$title<BR>";
$i++;
**

This will print all the results.

Finally, you should use this as a starting place and reference the PHP Docs ( http://www.php.net/manual/ ) if you want to do something... they have tons of useful functions implemented.

For example, if you have raw text in the database, and you want to output as html... you can replace all the carriage returns to <br>'s with

$newstring=nl2br($string);

To add backslashes to quotes and other special characters before inserting the database.

$newstring=AddSlashes($string);

(and the converse StripSlashes());

lots of good stuff in there.


arn


------------------
http://www.weeno.com - Learn Stuff You Don't Know
Promote Your Site (http://www.weeno.com/Pages/promote.shtml) - write an article, get new/interested visitors.

Arn
05-23-2000, 10:49 PM
Suresk,

a couple of problems with your code:

-------

$i = 0;
$query = "select blah from mydatatable WHERE id=$id";
$result = MYSQL_QUERY($query);
$number = MYSQL_NUM_ROWS($result);

while ($i < $number){
>>>> $query = "select blah from mydatatable WHERE id=$id";
>>>> $result = MYSQL_QUERY($query);

The above two lines are unnecessary

$blah = mysql_result($result,0,"blah");

you need to replace "0" with $i other wise you get the same result every time

$i++;

**


-----

Finally, the above will only select all the items with id=$id. I'd assume there is only 1 id=$id.

arn


------------------
http://www.weeno.com - Learn Stuff You Don't Know
Promote Your Site (http://www.weeno.com/Pages/promote.shtml) - write an article, get new/interested visitors.

[This message has been edited by Arn (edited 05-23-2000).]

Ken-
05-23-2000, 10:54 PM
Originally posted by Arn:

Great Ken! I didn't realize you had it all setup to go (php and mysql).


I've got all the pieces, I just need to put them together basically http://geekvillage.com/ubb/wink.gif

Everything works great, and that script will work perfectly for what I have planned. Once I get everything off the ground I'll take the time to learn everything I can.

Just one last question (hopefully)...I was planning on using the WebAdverts ad rotator, but it uses SSI to call the ads. Is it possible to use SSI in php files? From what I can tell off the bat it doesn't work, but there may be something I need to do that I'm not aware of.

If it isn't possible, I'll have to look for a PHP ad rotator.

Thanks again.

Arn
05-23-2000, 11:06 PM
Nope, I don't think you can call SSI from PHP... but I think you can call PHP from SSI.

<!--#include virtual="/includes/yourscript.php3" -->

This means, you have to move all your DB php code out... or your whole page could be:

-------
<!--#include your webadverts -->
<!--#include virtual="/includes/yourscript.php3" -->

-------

and do all your real coding in yourscript.php3

I've never used this, so you may have to play around with it.


arn



------------------
http://www.weeno.com - Learn Stuff You Don't Know
Promote Your Site (http://www.weeno.com/Pages/promote.shtml) - write an article, get new/interested visitors.

Ken-
05-24-2000, 10:50 AM
Arn,

On the WebAdverts forum I found a snippet of code that does exactly what I need. It acts as an SSI..

<?
virtual("/ads/A_ads.cgi");
?>

Where A_ads.cgi will display the banners I have specified to run in the "A" zone. You can set up any number of zones to run different ads in if you choose.

Would it be possible for me to have the "zone" letter set as a variable that I can specify through the URL?

I.e. http://site.com/script.php3?id=1&zone=A

I've tried a couple things but can't seem to get it right. If it isn't possible, it's really no big deal, but it would make things easier if an advertiser wanted to run banners on one specific page.

Ken

Arn
05-24-2000, 05:59 PM
Hi,

Try this:
http://www.site.com/script.php3?id=1&zone=A

<?

$filename = "/ads/" . $zone . "_ads.cgi";

virtual("$filename");
?>



------------------
http://www.weeno.com - Learn Stuff You Don't Know
Promote Your Site (http://www.weeno.com/Pages/promote.shtml) - write an article, get new/interested visitors.

Click Here!