viewing the data in the Database

@yosephbashaSep 21.2005

I am trying to get info from the database but it is not working.. can some one tell me what the proplem is?

// ========================================
// Get Inbound Variables
// ========================================
$txtcode = SHOW(“code”);
$txtdesciption= SHOW(“description”);
$txtprice= SHOW(“Price”);
$SEARCH_BUTTON = SHOW(“btnSearch”);

// Connect to DB
$digitalb_cpt = mysql_connect(‘localhost’, ‘digitalb_admin’, ‘admin’);

// ========================================
// Run Search SQL and Build Results
// ========================================
$rsSearchResults = mysql_query($sqlSearch, $db) or die(“$sqlSearch FAILED: ” . mysql_error());
$row_count = 0;

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

$row_count++;

$txtcode = $row[“code”]; $txtdesciption= str_replace(“\”, “”, $row[“description”]); $txtprice = $row[“Price”];

if ($row_count % 2) { $bgcolor = “#FFFFFF”; } else { $bgcolor = “#F2F2F2”; }

$htmlViewNoteLink = “view_note.php?txtNoteId=$note_id”;

$htmlSearchResults .= ” <tr bgcolor=$bgcolor>n”; $htmlSearchResults .= ” <td class=’SearchResults’>$txtcode.</td>n”; $htmlSearchResults .= ” <td class=’SearchResults’><a href=’$htmlViewNoteLink’>$txtdesciption</a></td>n”; $htmlSearchResults .= ” <td class=’SearchResults’>$txtprice </td>n”; $htmlSearchResults .= ” </tr>n”;

} @mysql_free_result($rsSearchResults);

// ========================================
// If No Results, Set Default
// ========================================
if ($htmlSearchResults == “” && $SEARCH_BUTTON != “”) {
$htmlSearchResults .= ” <tr>n”;
$htmlSearchResults .= ” <td class=’SearchResults’ colspan=’6′><I>No Notes Found. Please run query again…</I></td>n”;
$htmlSearchResults .= ” </tr>n”;
}

if ($htmlSearchResults == “” && $SEARCH_BUTTON == “”) {
$htmlSearchResults .= ” <tr>n”;
$htmlSearchResults .= ” <td class=’SearchResults’ colspan=’6′><I>Please use the form above to search for Notes.</I></td>n”;
$htmlSearchResults .= ” </tr>n”;
}
?>
<table width=”100%”>
<tr><td><form method=”get”>
<p><span class=”SmallHeader”>Search For Notes By</span></p>
<form method=”get”>
<table width=”400″ border=”0″ cellspacing=”0″ cellpadding=”1″ bordercolor=”#666666″>
<tr bgcolor=”#666666″>
<td>
<table width=”100%” border=”0″ cellspacing=”2″ cellpadding=”1″ bgcolor=”#F4F4F4″>
<tr valign=”middle”>
<td width=”50%” class=”FormText”>CPT Code:</td>
<td width=”50%”>
<input type=”text” name=”txtcode” value=”<?=$TITLE?>”>
</td>
</tr>
<tr valign=”middle”>
<td width=”50%” class=”FormText”>KeywordS:</td>
<td>
<input type=”text” name=”txtdesciption” value=”<?=$TEXT?>”>
</td>
</tr>
<tr valign=”middle”>
<td width=”50%” class=”FormText”>Price:</td>
<td class=”FormText”>
<input type=”text” name=”txtprice ” value=”<?=$CLASS_DATE?>”></td>
</tr>
<tr>
<td> </td>
<td>
<input type=”submit” name=”btnSearch” value=”SEARCH >>”>
</td>
</tr>
</table>
</td>
</tr>
</form>
</table>
<p> </p>
<table width=”100%” border=”0″ cellspacing=”1″ cellpadding=”1″>
<tr>
<td width=”5%” class=”SearchResultsHeader”> Code</td>
<td class=”SearchResultsHeader”> Description</td>
<td class=”SearchResultsHeader”> Price</td>
</tr>
<?=$htmlSearchResults ?>
</table>
<BR>

</td>

</tr>
</form>
</table>

to post a comment

PHP

8 Comments(s) _↴

@bathurst_guySep 21.2005 — #you dont specify your sql anywhere in this script, that may be your problem, you need to specify it in $sqlSearch

@yosephbashaauthorSep 21.2005 — #okay i did that but i am still getting this error

Fatal error: Call to undefined function: show() in /home/digitalb/public_html/test/findcpt.php on line 6

here is my new code

<?

// ========================================

// Get Inbound Variables

// ========================================

$txtcode = SHOW("code");

$txtdesciption= SHOW("description");

$txtprice= SHOW("Price");

$SEARCH_BUTTON = SHOW("btnSearch");

// Connect to DB

$digitalb_cpt = mysql_connect('localhost', 'digitalb_admin', 'admin');

// ========================================

// Run Search SQL and Build Results

// ========================================

$rsSearchResults = mysql_query($sqlSearch, $db) or die("$sqlSearch FAILED: " . mysql_error());

$row_count = 0;

while ( $row = mysql_fetch_array($rsSearchResults) ) {
 
   $row_count++;
 
   $txtcode = $row["code"];
   $txtdesciption= str_replace("\", "", $row["description"]);
   $txtprice = $row["Price"];
 
 
   if ($row_count % 2) {
   $bgcolor = "#FFFFFF";
   } else {
   $bgcolor = "#F2F2F2";
   }
 
   $htmlViewNoteLink = "view_note.php?txtNoteId=$note_id";
 
   $htmlSearchResults .= "	<tr bgcolor=$bgcolor>n";
   $htmlSearchResults .= "	  <td class='SearchResults'>$txtcode.</td>n";
   $htmlSearchResults .= "	  <td class='SearchResults'><a href='$htmlViewNoteLink'>$txtdesciption</a></td>n";
   $htmlSearchResults .= "	  <td class='SearchResults'>$txtprice </td>n";
   $htmlSearchResults .= "	</tr>n";
 
  }
  @mysql_free_result($rsSearchResults);

// ========================================

// Build Search SQL

// ========================================

$sqlSearch = "

SELECT

code

description

Price

 FROM
   cpt_code   //table
 
   WHERE
   $txtcode
   $txtdesciption
   $txtprice
 
   ";
 
  if ($TITLE != "") {
   $sqlSearch .= " AND n.title like '%$TITLE%' ";
  }
  if ($TEXT != "") {
   $sqlSearch .= " AND n.text like '%$TEXT%' ";
  }
  if ($CLASS_ID != "") {
   $sqlSearch .= " AND n.class_id = '$CLASS_ID' ";
  }
  if ($CLASS_DATE != "") {
   $sqlSearch .= " AND n.class_date = $DB_CLASS_DATE ";
  }
  $sqlSearch .= " ORDER BY n.class_date desc";
 
  if ($SEARCH_BUTTON =="") {
   $sqlSearch .= " LIMIT 0, 20 ";
  }

// ========================================

// If No Results, Set Default

// ========================================

if ($htmlSearchResults == "" && $SEARCH_BUTTON != "") {

$htmlSearchResults .= " <tr>n";

$htmlSearchResults .= " <td class='SearchResults' colspan='6'><I>No Notes Found. Please run query again...</I></td>n";

$htmlSearchResults .= " </tr>n";

}

if ($htmlSearchResults == "" && $SEARCH_BUTTON == "") {

$htmlSearchResults .= " <tr>n";

$htmlSearchResults .= " <td class='SearchResults' colspan='6'><I>Please use the form above to search for Notes.</I></td>n";

$htmlSearchResults .= " </tr>n";

}

// ========================================

// If Search, Get Results

// ========================================

$htmlSearchResults = "";

?>

<table width="100%">

<tr><td><form method="get">

<p><span class="SmallHeader">Search For CPT By</span></p>

<form method="get">

<table width="400" border="0" cellspacing="0" cellpadding="1" bordercolor="#666666">

<tr bgcolor="#666666">

<td>

<table width="100%" border="0" cellspacing="2" cellpadding="1" bgcolor="#F4F4F4">

<tr valign="middle">

<td width="50%" class="FormText">CPT Code:</td>

<td width="50%">

<input type="text" name="txtcode" value="<?=$TITLE?>">

</td>

</tr>

<tr valign="middle">

<td width="50%" class="FormText">KeywordS:</td>

<td>

<input type="text" name="txtdesciption" value="<?=$TEXT?>">

</td>

</tr>

<tr valign="middle">

<td width="50%" class="FormText">Price:</td>

<td class="FormText">

<input type="text" name="txtprice " value="<?=$CLASS_DATE?>"></td>

</tr>

<tr>

<td> </td>

<td>

<input type="submit" name="btnSearch" value="SEARCH >>">

</td>

</tr>

</table>

</td>

</tr>

</form>

</table>

<p> </p>

<table width="100%" border="0" cellspacing="1" cellpadding="1">

<tr>

<td width="12%" class="SearchResultsHeader"> Code</td>

<td class="SearchResultsHeader"> Description</td>

<td class="SearchResultsHeader"> Price</td>

</tr>

<?=$htmlSearchResults ?>

</table>

<BR>

</td>
</tr>

</form>

</table>

<?

you dont specify your sql anywhere in this script, that may be your problem, you need to specify it in $sqlSearch[/QUOTE]

@ian_hSep 21.2005 — #There are a couple of things I think:

Firstly your database connect could use tidying up and securing:

1) replace $digitalb_cpt = mysql_connect('localhost', 'digitalb_admin', 'admin'); with:

/* declare db variables. At some point for security reasons put this in a PHP require file [B]above [/B] your publicly accessible folder*/

$DBhost = "localhost";

$DBuser = "digitalb_admin";

$DBpass = "admin";

$DBName = "The Name of the Database";

$table = "The Name of the Table";

$digitalb_cpt = mysql_connect($DBhost,$DBuser,$DBpass) or die("Unable to connect to database");

2) Select your database (this was not in your code)

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

3) define your SQL statement as $sqlSearch (use php includes/requires to make your code more readable)

$sqlSearch = "whatever";

4) do your query:

$rsSearchResults = mysql_query($sqlSearch);

5) close your connection

mysql_close($digitalb_cpt);

Secondly you get a call to an undefined function. This is because when PHP sees show("code") it goes off looking for a function called show(). I guess you are actually looking for incoming for variables from a form? If so try something like: $txtcode = $_POST['txtcode'];

Hope that helps.

@yosephbashaauthorSep 21.2005 — #I made the adjustments and now it is unable to connect to database why?

<?

// ========================================

// Get Inbound Variables

// ========================================

$txtcode = $_POST["code"];

$txtdesciption= $_POST["description"];

$txtprice= $_POST["Price"];

$SEARCH_BUTTON = $_POST["btnSearch"];

// Connect to DB

/* declare db variables. At some point for security reasons put this in a PHP require file above your publicly accessible folder*/

$DBhost = "localhost";

$DBuser = "digitalb_admin";

$DBpass = "admin";

$DBName = "digitalb_cpt";

$table = "cpt_code";

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

// ========================================

// Run Search SQL and Build Results

// ========================================

$rsSearchResults = mysql_query($sqlSearch, $db) or die("$sqlSearch FAILED: " . mysql_error());

$row_count = 0;

while ( $row = mysql_fetch_array($rsSearchResults) ) {
 
   $row_count++;
 
   $txtcode = $row["code"];
   $txtdesciption= str_replace("\", "", $row["description"]);
   $txtprice = $row["Price"];
 
 
   if ($row_count % 2) {
   $bgcolor = "#FFFFFF";
   } else {
   $bgcolor = "#F2F2F2";
   }
 
   $htmlViewNoteLink = "view_note.php?txtNoteId=$note_id";
 
   $htmlSearchResults .= "	<tr bgcolor=$bgcolor>n";
   $htmlSearchResults .= "	  <td class='SearchResults'>$txtcode.</td>n";
   $htmlSearchResults .= "	  <td class='SearchResults'><a href='$htmlViewNoteLink'>$txtdesciption</a></td>n";
   $htmlSearchResults .= "	  <td class='SearchResults'>$txtprice </td>n";
   $htmlSearchResults .= "	</tr>n";
 
  }
  @mysql_free_result($rsSearchResults);

// ========================================

// Build Search SQL

// ========================================

$sqlSearch = "

SELECT

code

description

Price

 FROM
   cpt_code   //table
 
   WHERE
   $txtcode
   $txtdesciption
   $txtprice
 
   ";
 
  if ($TITLE != "") {
   $sqlSearch .= " AND n.title like '%$txtcode%' ";
  }
  if ($TEXT != "") {
   $sqlSearch .= " AND n.text like '%$txtdesciption%' ";
  }
  if ($CLASS_ID != "") {
   $sqlSearch .= " AND n.class_id = '$txtprice' ";
  }
  $sqlSearch .= " ORDER BY n.class_date desc";
 
  if ($SEARCH_BUTTON =="") {
   $sqlSearch .= " LIMIT 0, 20 ";
  }

@ian_hSep 21.2005 — #From your post it seems you missed this line out:

$digitalb_cpt = mysql_connect($DBhost,$DBuser,$DBpass) or die("Unable to connect to database");

Put the above statement into your code beneath the list of database variables.

If that still doesn't work, try removing the quotes around the $DBName in the @mysql_select_db statement. If it still doesnt work, try building a really simple connect page to test everything, something like:

<?php

$link = mysql_connect('localhost', 'digitalb_admin', 'admin');

if (!$link) {

die('Could not connect: ' . mysql_error());

} else {

echo 'Connected to MySQL successfully'; }

$db_selected = mysql_select_db('digitalb_cpt', $link);

if (!$db_selected) {

die ('Can't use database : ' . mysql_error());

} else {

echo 'Connected to the database ok'; }

mysql_close($link);

?>

Its the end of my day and I'm v tired, so no guarantees on any of the above!

@yosephbashaauthorSep 21.2005 — #I added that and now it gave me this error

Warning: mysql_query(): supplied argument is not a valid MySQL-Link resource in /home/digitalb/public_html/test/findcpt.php on line 27

FAILED:

OH MAN THIS IS FRUSTRATING?

From your post it seems you missed this line out:

$digitalb_cpt = mysql_connect($DBhost,$DBuser,$DBpass) or die("Unable to connect to database");

Put the above statement into your code beneath the list of database variables.

If that still doesn't work, try removing the quotes around the $DBName in the @mysql_select_db statement. If it still doesnt work, try building a really simple connect page to test everything, something like:

<?php

$link = mysql_connect('localhost', 'digitalb_admin', 'admin');

if (!$link) {

die('Could not connect: ' . mysql_error());

} else {

echo 'Connected to MySQL successfully'; }

$db_selected = mysql_select_db('digitalb_cpt', $link);

if (!$db_selected) {

die ('Can't use database : ' . mysql_error());

} else {

echo 'Connected to the database ok'; }

mysql_close($link);

?>

Its the end of my day and I'm v tired, so no guarantees on any of the above![/QUOTE]

@bathurst_guySep 22.2005 — #well its connecting to the database now but the query is not returning any results or is incorrect.

You need commas between each of your tables and field names for starters, and the where clause is wrong.

Have you even learnt [url=http://resources.sdstudio.com.au/mysql.php]MySQL[/url]?

@ian_hSep 22.2005 — #Morning, glad to see you got the connection working. Troubleshooting can be like that - one thing at a time.

Re: your SQL statement - it is dodgy and needs (lots of) work. I tend to build these things up one element at a time making sure each element works before adding the nest layer of complexity. It feels slow but it sure cuts down the time spent hunting for bugs.

Try this as your SQL statement (tip, don't put comments inside quote marks!)

$sqlSearch = "SELECT code, description, Price FROM ".$table." WHERE";

if ($title != "")

{

$sqlSearch .= " AND title like '%$txtcode%' ";

}

echo $sqlSearch;

//this should echo the $sqlSearch query to the top of the page allowing you

//to understand if the query is wrong. Helps debugging!

I have to echo bathurst_guy on this - how much PHP/MySQL experience do you have as what you are trying to do is quite complex as an early project. I still don't understand where the incomign variables are coming from and I also don't understand where other variables liek $title are coming from.

Also in #PHP _↴

MYSQL join finishing an unfinished project - high scoring recommendations for calendar?

Success!

Help @yosephbasha spread the word by sharing this article on Twitter...

Tweet This

viewing the data in the Database

8 Comments(s) _↴

Also in #PHP _↴

Success!

Social

Version

viewing the data in the Database

8 Comments(s) ↴

Also in #PHP ↴

Success!

The web is an endless sea of information. Don't miss the boat... Subscribe!

Social

Version

8 Comments(s) _↴

Also in #PHP _↴