[SOLVED] Basic database to call information

hausjellp · #1 Apr 27th, 2008, 20:45

Hello,

First post here

I have built quite a few websites but I just cant work out this:

I would like to have a box where a user enters a number, e.g.. UK2008-10 and clicks search. Once search is pressed set information is then returned and displayed.(from a db which has not yet been built, any ideas what to use?)

Example:

[UK2008-10 ] [Search]

Returned Data:

Name
Address
'Static' Text

Can anyone shed some light on this?

I normally use HTML or CMS's to build websites but Im a quick learner.

Regards,

Piers

ps: sorry if this question if too basic

saltedm8 · #2 Apr 27th, 2008, 21:31

moving to php forum

hausjellp · #3 Apr 27th, 2008, 21:44

sorry, didnt mean to post in the wrong section

saltedm8 · #4 Apr 27th, 2008, 22:31

no problem, most dynamic data if being called from the database is a php issue, - but not all, sometimes it can be asp etc

hausjellp · #5 Apr 27th, 2008, 23:34

ok, i have created a database using phpMyAdmin. I have created 1 table called 'userid' with 3 fields. These are called; fldID(primary), fldName and fldAddress.

I have setup a search box. I search from table 'userid' for field 'fldID' to check if its there, if so then it returns the fldID information.

What im stuck on now is how to just search for the fldID but return fldName and fldAddress info too - is this possible?

Example:

[UK2008J ] [SEARCH]

Next page:

User ID: UK2008

-----------------

What i would like to have is:

[UK2008J ] [SEARCH]

Next page:

User ID: UK2008
User Name: Adam Smith
Address: Street, Town, County

-------------------

Would it make more sense to have 3 tables? One for name, one for user id and one for address?

Any help with this is appreciated!

BRONIC · #6 Apr 28th, 2008, 13:21

I think that this is what you are looking for

replace $host with your db host,
$username with your database username
and $password with your database password
also $database with your database name.

PHP: Select all


$link = mysql_connect($host,$username,$password);

@mysql_select_db($database) or die("Unable to find database because:    " . mysql_error());

$query="SELECT * FROM userid WHERE fldId = '$_POST[id]'";

$result = mysql_query($query) or die(mysql_error());;

if(mysql_num_rows($res)==0) {

    $error = "The user does not exist";

}

else {

    $row = mysql_fetch_assoc($result);

    $id = $row[fldId];

            $name = $row[fldName];

            $address = $row[fldAddress];

    mysql_close($link);

}

print "User ID: " . $id . "<br>User Name: " . $name . "<br>Address: " . $address . "<br>";

hope this helps

hausjellp · #7 Apr 29th, 2008, 22:18

THANK YOU!

Im just trying to set that up now

Regards,

P.

hausjellp · #8 Apr 29th, 2008, 22:38

I seem to have got a bit stuck with this.

dab42pat · #9 Apr 29th, 2008, 22:53

In what way?

hausjellp · #10 Apr 30th, 2008, 00:11

I have been using this php:

Code: Select all

<?php

  $var = @$_GET['q'] ;
  $trimmed = trim($var);

$limit=1; 

if ($trimmed == "No Data")
  {
  echo "<p>Please enter a search...</p>";
  exit;
  }

if (!isset($var))
  {
  echo "<p>We dont seem to have a search parameter!</p>";
  exit;
  }

mysql_connect("hidden","hidden","hidden"); 

mysql_select_db("hidden") or die("Unable to select database"); 

$query = "select * from userid where fldID like \"%$trimmed%\"  
  order by fldID"; 

 $numresults=mysql_query($query);
 $numrows=mysql_num_rows($numresults);


if ($numrows == 3)
  {
  echo "<h4>Results</h4>";
  echo "<p>Sorry, your search: &quot;" . $trimmed . "&quot; returned zero results</p>";

 echo "<p><a href=\"http://www.google.com/search?q=" 
  . $trimmed . "\" target=\"_blank\" title=\"Look up 
  " . $trimmed . " on Google\">Click here</a> to try the 
  search on google</p>";
  }

  if (empty($s)) {
  $s=0;
  }

  $query .= " limit $s,$limit";
  $result = mysql_query($query) or die("Couldn't execute query");

echo "<p>You searched for: &quot;" . $var . "&quot;</p>";

echo "Member Name";


  while ($row= mysql_fetch_array($result)) {
  $title = $row["fldName"];

  echo "$count:&nbsp;$title" ;
  $count++ ;
  }

$currPage = (($s/$limit) + 1);

  echo "<br />";

  if ($s>=1) { // bypass PREV link if s is 0
  $prevs=($s-$limit);
  print "&nbsp;<a href=\"$PHP_SELF?s=$prevs&q=$var\">&lt;&lt; 
  Prev 10</a>&nbsp&nbsp;";
  }

  $pages=intval($numrows/$limit);


  if ($numrows%$limit) {
  $pages++;
  }

  if (!((($s+$limit)/$limit)==$pages) && $pages!=1) {

  $news=$s+$limit;

  echo "&nbsp;<a href=\"$PHP_SELF?s=$news&q=$var\">Next 10 &gt;&gt;</a>";
  }

$a = $s + ($limit) ;
  if ($a > $numrows) { $a = $numrows ; }
  $b = $s + 1 ;
  echo "<p>Showing results $b to $a of $numrows</p>";

?>

and this html:

Code: Select all

<body>
<form name="form" action="search.php" method="get">
  <input type="text" name="q" />
  <input type="submit" name="Submit" value="Search" />
</form>
</body>

I have tried working with the code provided above but got all confused. It makes sense but I just cant make sense of it. (not sure if that even makes sense) lol

BRONIC · #11 Apr 30th, 2008, 02:05

i got it to work here:

http://www.myfreeportfolio.net/test/search.php

there is only one entry though, the user id is 28627

here is the code:

PHP: Select all


<?php

if($_GET['action'] == "search") {

  $var = $_POST['q'] ;

  $trimmed = trim($var);

    $host = "localhost";

    $db = "test";

    $usr = "username";

    $pss = "pass";

$limit=1; 

if (!$trimmed)

  {

  echo "<p>Please enter a search...</p>";

  exit;

  }



if (!isset($var))

  {

  echo "<p>We dont seem to have a search parameter!</p>";

  exit;

  }



mysql_connect($host,$usr,$pss); 



mysql_select_db($db) or die("Unable to select database"); 



$query = "select * from test where fldID like \"%$trimmed%\"  

  order by fldID"; 



 $numresults=mysql_query($query);

 $numrows=mysql_num_rows($numresults);





if ($numrows == 0)

  {

  echo "<h4>Results</h4>";

  echo "<p>Sorry, your search: &quot;" . $trimmed . "&quot; returned zero results</p>";



 echo "<p><a href=\"http://www.google.com/search?q=" 

  . $trimmed . "\" target=\"_blank\" title=\"Look up 

  " . $trimmed . " on Google\">Click here</a> to try the 

  search on google</p>";

  }



  if (empty($s)) {

  $s=0;

  }



  $query .= " limit $s,$limit";

  $result = mysql_query($query) or die("Couldn't execute query");



echo "<p>You searched for: &quot;" . $var . "&quot;</p>";



echo "Member Name";





  while ($row= mysql_fetch_array($result)) {

  $title = $row["fldName"];



  echo "$count:&nbsp;$title" ;

  $count++ ;

  }



$currPage = (($s/$limit) + 1);



  echo "<br />";



  if ($s>=1) { // bypass PREV link if s is 0

  $prevs=($s-$limit);

  print "&nbsp;<a href=\"$PHP_SELF?s=$prevs&q=$var\">&lt;&lt; 

  Prev 10</a>&nbsp&nbsp;";

  }



  $pages=intval($numrows/$limit);





  if ($numrows%$limit) {

  $pages++;

  }



  if (!((($s+$limit)/$limit)==$pages) && $pages!=1) {



  $news=$s+$limit;



  echo "&nbsp;<a href=\"$PHP_SELF?s=$news&q=$var\">Next 10 &gt;&gt;</a>";

  }



$a = $s + ($limit) ;

  if ($a > $numrows) { $a = $numrows ; }

  $b = $s + 1 ;

  echo "<p>Showing results $b to $a of $numrows</p>";

}

?>

and the form:

HTML: Select all

<body>
<form name="form" action="search.php?action=search" method="POST">
  <input type="text" name="q" />
  <input type="submit" name="Submit" value="Search" />
</form>
</body>

i hope it works for you, this code is in all one file called search.php

hausjellp · #12 Apr 30th, 2008, 02:26

Thank you!

If i wanted to make a couple of changes, for example, search for 1 user id and instead of just the name, it brings the name, address, status (verified or expired) and expire date, what would i add? I have played around with what you created but with no luck.

For example:

-------------------------

Information for User ID: [fldID]

Member Name: [fldName]
Address: [fldAddress]
Expires: [fldStatus]
Member Status: [fldStatus]

---------------------------

Also I did try and make it so only exact user id's entered would work. With the current code if you just enter '28' instead of '28627' the info is still fetched.

Hope that makes some sense,

Regards,

Piers

BRONIC · #13 Apr 30th, 2008, 02:51

that makes perfect sense, but right now i have a report to type by midnight so i shall have the solution for you by tommorow.

hausjellp · #14 Apr 30th, 2008, 03:04

Bronic - Thank you so very much. I look forward to seeing it

Good luck with the report.

Regards,

Piers

BRONIC · #15 Apr 30th, 2008, 14:27

this is very simple, your code now is set up great and this is just a tweak.

here its updated:
http://www.myfreeportfolio.net/test/search.php
there are two entries:
28627
and
987

all you needed to do was replace:

PHP: Select all


$query = "select * from test where fldID like \"%$trimmed%\"  



  order by fldID";

with:

PHP: Select all


$query = "select * from test where fldID = '$trimmed' order by fldID";

that will return only the specified searched term

to get all of the field you need to replace:

PHP: Select all


 while ($row= mysql_fetch_array($result)) {



  $title = $row["fldName"];







  echo "$count:&nbsp;$title" ;



  $count++ ;

with:

PHP: Select all


while ($row= mysql_fetch_array($result)) {

  $Name = $row["fldName"];

  $Id

		Webforumz.com > Main Forums > Program Your Website > PHP Forum
[SOLVED] Basic database to call information