Page 1 of 1

Calling values from a hyperlink and querying mysql?

Posted: Sun Nov 02, 2003 1:15 pm
by hibbard
Hello, I have a nice question for the forum leaders :), so here goes:

Let's say you have 4 hyperlinks on your page :
Bob
Joe
Smithers
Lisa


Now, each of these links are values in my mysql database under the header value "Names".

What I want to do, is when I click on the hyperlink Bob, i want to be able to query mysql looking for bob and then posting the info that is with him in his row on a new page.

The thing is, bob is not a static name on the page. bob could be jon tomorrow, so the links are dynamic in other words ( they will change ).




the only idea i could come up wtih is setting each link to be directed to a php page ( call it query.php ), and inside that page it will read the link you clicked on, and query the database from there... however, since i'm a newb to this, i have no idea how to go about doing it...

if someone could give me some insight on this, it would be greatly appreciated.. Thanks

Posted: Sun Nov 02, 2003 1:21 pm
by hibbard
deleted

Posted: Sun Nov 02, 2003 2:38 pm
by hibbard
ok, i found this code, but it doesn't work as i need it to? any help would be appreciated.....

Code: Select all

#######  List.php ###### 
<?php

@ $link = mysql_pconnect("host","user","pass"); 

$db = mysql_select_db("database"); 

//* Get all records 
$sql = "SELECT * FROM bob"; 

$sql_result = mysql_query($sql); 

while ($row = mysql_fetch_array($sql_result)) &#123; 
//* In place of $id u would put the name of the column that u want displayed, remember you can use multiple columns when creating these 
    $id = $row&#1111;"name"]; 
//* echo the link to html 
        echo "<a href="detail.php?name=$name&id=$id">$id</a>"; 
    &#125;; 
mysql_free_result($sql_result); 
mysql_close($link); 
?> 
############ Detail.php ############ 
<?php 
@ $link = mysql_pconnect("$host","$user","$pass"); 
$db = mysql_select_db("database"); 
$sql = "SELECT * FROM bob where name = '$id'"; 

$sql_result = mysql_query($sql); 

while ($row = mysql_fetch_array($sql_result)) &#123; 
    $detail = $row&#1111;"detail"]; 
    $detail1 = $row&#1111;"detail1"]; 
    $detail2 = $row&#1111;"detail2"]; 
        echo " 
Detail : $detail 
Other Detail : $detail1 
MORE Detail : $detail2 
"; 
    &#125;; 
mysql_free_result($sql_result); 
mysql_close($link); 
?>

I changed the database to my database's name and the table as well, and also changed the login/pass/host for mysql to reflect my own, but it doesn't show what i need.

I changed the value of $id to 'name'. it will list all the values in the field 'name', but when i click on the name, it does not display the information related to name on the next page... is there something i'm missing here?



**************EDIT***************

when i try to call the variable $ID from describe.php, it does not return any value at all. what do i need to do different in order to call this variable from the describe page?

Posted: Mon Nov 03, 2003 3:17 am
by twigletmac
Try this:

Code: Select all

#######  List.php ######
<?php

// - add error handling to the main mysql_xxx functions
// - if you're not going to use the $link variable you may as well not
//   bother adding it in
@mysql_connect('host', 'user', 'pass') or die(mysql_error());
@mysql_select_db('database') or die(mysql_error());

// - select only the columns you need from the table
// - you should really have a more unique column than name (as more than one
//   person may have the same name) to use to identify the records.
$sql = "SELECT name FROM bob";

$result = mysql_query($sql) or die(mysql_error().'<p>'.$sql.'</p>');

while ($row = mysql_fetch_array($result)) {
	// don't need to assign a variable if you're only going to use it once
	echo '<a href="detail.php?name='.$row['name'].'">'.$row['name'].'</a>';
}

?>
############ Detail.php ############
<?php
// make sure that there is a name to search for
if (!empty($_GET['name'])) {
	// get the name from the query string
	$name = $_GET['name'];

	// shouldn't have quotes when all just variables are concerned $host, not
	// "$host"
	@mysql_connect($host, $user, $pass) or die(mysql_error());
	@mysql_select_db('database') or die(mysql_error());

	// again, select only what you need
	$sql = "SELECT detail, detail1, detail2 FROM bob where name = '$name'";

	$result = mysql_query($sql) or die(mysql_error().'<p>'.$sql.'</p>');

	// check to make sure you've got a result and print the info if you do
	if (mysql_num_rows($result) == 1) {
		$row = mysql_fetch_assoc($result);

		echo 'Detail: '.$row['detail'].'<br />';
		echo 'Other Detail: '.$row['detail1'].'<br />';
		echo 'More Detail: '.$row['detail2'].'<br />';

	} else {
		// if the name wasn't in the DB
		echo '<p><a href="List.php">Please choose a name from the list.</a></p>';
	}
} else {
	// if a name wasn't selected
	echo '<p><a href="List.php">Please choose a name from the list.</a></p>';
}

?>
Mac

Posted: Mon Nov 03, 2003 9:37 am
by infolock
you are the WOMAN mac :D thanks a lot for the great help! The only problem with that code is, if there is a multiple instance of the same name, the page "describe.php" returns nill saying "please select a name from the list" linking back to "list.php".

so if i have the order as follows :

bob
bob
bob
sally
joe


bob won't be able to be displayed. any thoughts?

Posted: Mon Nov 03, 2003 9:43 am
by JAM
infolock wrote:you are the MAN mac :D thanks a lot for the great help!
My lips are sealed... :wink:

Posted: Mon Nov 03, 2003 9:45 am
by twigletmac
infolock wrote:MAN
woMAN ;) :lol:

Posted: Mon Nov 03, 2003 9:45 am
by scorphus
infolock wrote:you are the MAN mac :D thanks a lot for the great help!
This kindly and gentle forum mate who just helped you is a girl ;)

Posted: Mon Nov 03, 2003 9:48 am
by twigletmac
infolock wrote:you are the MAN mac :D thanks a lot for the great help! The only problem with that code is, if there is a multiple instance of the same name, the page "describe.php" returns nill saying "please select a name from the list" linking back to "list.php".

so if i have the order as follows :

bob
bob
bob
sally
joe


bob won't be able to be displayed. any thoughts?
Back to the topic :lol:, do you have an ID field for each person that is unique, eg. an autoincrementing INT field? That would be the better field to use as a link as it could not have duplicate values in it.

Mac

Posted: Mon Nov 03, 2003 9:53 am
by infolock
nope, but see i need it this way. i'll explain it better now that we got going.

what i'm doing is parsing a log file, posting it to mysql, and then retrieving it in an html. so, what i get is multiple instances of the same ip ( or name in this case ) accessing my server at a time. so after all is said and done, i'm making a report, but the only way to do that is to keep each ip in the database ( even if there are mutliple instances ).

so, you would have a db that looks like this:
*** IP *** - *** date ** - ****** Action ********
205.100.40.1 - 10-05-2003 - downloaded file bob.html
205.100.40.1 - 10-08-2003 - downloaded file b55.html
205.100.40.1 - 10-08-2003 - downloaded file b56.html
205.100.40.1 - 10-08-2003 - downloaded file b67.html
225.111.10.1 - 10-08-2003 - downloaded file 234nas.html
210.200.15.5 - 10-08-2003 - downloaded file asd9er.html


so the multiple records of the same ip are needed...

so, in other words everywhere you see 'name' above, you would replace that with 'ip' or $ip.

hope that makes sense as to why i dont have incrementing fields.



***edit****
man comment updated to woman. sorry there :)

and, i'm off to school now so i won't be able to respond until around 1:30-2pm. thanks for all of your help mac :)

Posted: Mon Nov 03, 2003 3:41 pm
by twigletmac
I'll try a reply again (my computer decided to restart itself just as I finished the last one :?).

Instead of:

Code: Select all

if (mysql_num_rows($result) == 1) {
      $row = mysql_fetch_assoc($result);

      echo 'Detail: '.$row['detail'].'<br />';
      echo 'Other Detail: '.$row['detail1'].'<br />';
      echo 'More Detail: '.$row['detail2'].'<br />';

   }
try

Code: Select all

// check to make sure there's at least one result
    if (mysql_num_rows($result) > 0) {
       echo '<h1>Number of Results: '.mysql_num_rows($result).'</h1>';

       // loop through the result set for display
       while ($row = mysql_fetch_assoc($result)) {
            echo '<p>Detail: '.$row['detail'].'<br />';
            echo 'Other Detail: '.$row['detail1'].'<br />';
            echo 'More Detail: '.$row['detail2'].'</p>';
       }
   }
Mac

Posted: Mon Nov 03, 2003 4:05 pm
by infolock
works like a charm. thanks again :)