[SOLVED] Help -- PHP and ODBC Database application

wulfgar · December 20, 2006

Hi everyone. Basically im working on a project at the moment for a friend to develop a dynamic search engine for a local karate club which searches for students within an access database according to a users string input. Once the search button is activated a dynamic page displays the search results as just lastname, firstname format and then allows the user to;

1. view all relevant information regarding a student,
2. edit any information regarding a student,
3. delete a student's information from the database.

Thus far i have created a simple search engine which successfully searches the access database using a query which matches any substring which is the nearest match (using the LIKE clause) to any of the student entries within the database. A dynamic page is displayed with all of the relevant results and three button's are clearly available for each result (if more than one result);

1. view
2. edit
3. delete.

The gist of the code is shown below thus far;

<?
$FName = $_REQUEST['FName'];
$LName = $_REQUEST['LName'];

$data = 0;

if ($FName != "")
{
$data++;
}

if ($LName != "")
{
$data++;
}

$conn = odbc_connect('student_db', '', '') or die("<br/><b>Database Initialization failed</b>");

$query = "SELECT * from stu_db WHERE F_Name LIKE '$FName%' AND L_Name LIKE '$LName%'";

$rs = odbc_exec($conn,$query);

while(odbc_fetch_row($rs))
{
//variables store search results, second parameter is actual database coloumn names
$NFirst = odbc_result($rs,"F_Name");
$NLast = odbc_result($rs,"L_Name");

echo "<html>
<head>
<link href=\"stylesheet.css\" rel=\"stylesheet\" type=\"text/css\">
</head>
<body>
<table border=\"0\" class=\"tablex\" width=\"400px\" cellpadding=\"0\">
<tr><td><b>$NLast</b>, $NFirst</td>
<td align=\"right\" valign=\"top\" width=\"10\">
<form name=\"view\" method=\"post\" action=\"viewDetail.php\">
<input type=\"submit\" value=\"View\" class=\"button\">
</form>
<td align=\"right\" valign=\"top\" width=\"10\"><form name=\"edit\" method=\"post\" action=\"viewEdit.php\">
<input type=\"submit\" value=\"Edit\" class=\"button\">
</form></td>
<td align=\"right\" valign=\"top\" width=\"10\"><form name=\"delete\" method=\"post\" action=\"\">
<input type=\"submit\" value=\"Delete\" class=\"button\">
</form></td>
</td></tr>
</table>
</body>
</html>";
}
?>

The next part in which i am delving into requires a new dynamic page to be displayed showing the full results of the desired students details from the database. I have successfully created a query which will search and display the correct information as shown below.

$queryView = "SELECT * from stu_db WHERE F_Name = '$NFirst' AND L_Name = '$NLast'";

Although, this query will only work in conjunction with the variables $NFirst and $NLast which are located within this current script. I would like the results to be displayed on a new generated page, although my php skills are quite limited as i am a beginner, and the only way in which i can foresee this successfully happening is if i can pass the value of the two variables $NFirst and $Nlast to an alternate script (have no idea how too, and am not even sure if it is possible).

If any PHP guru's out there could please give me some assistance i would greatly appreciate it; as i mentioned my current PHP skills are very limited so please be understanding :D.

Regards

James.

tracy · December 20, 2006

Are you saying that you want to have one page with the last and first name only using php AND
another page with first and last name and other info? If so, it's simply a matter of displaying more cells in the database, right?
If not, maybe I misunderstood the question. Sorry.

Round · December 20, 2006

I think you want to create a list and hide the variables so that they can be passed to new page:-

#retrieve data
while ( $row = odbc_fetch_array( $rs ) )
{
$list = "<table>";
$list .= "<tr>";
$list .= "<td class=table>".$row["Nfirst"]."</td>";
$list .= "<td class=table>".$row["nlast"]."<td>";
$list .= "<td class=table><br><form action=\"studentDetails.php\" method=\"post\"><input type=\"hidden\" name=\"Nfirst\" value=\"".$row["Nfirst"]."\"><input type=\"hidden\" name=\"Nlast\" value=\"".$row["Nlast"]."\"><input type=\"submit\" value=\"View\"></form></td>";
$list .= "</tr>";
}
$list .= "</table>";

#list details
echo( $list );

then retrieve the variables on new page studentdetails.php and use them to carry a new query that will retrieve all details

$Nfirst = $_POST['Nfirst']
$Nlast = $_POST['Nlast']

or you could set up cookies but requires more knowledge :)

wulfgar · December 20, 2006

To your question Tracy yes :D thats exactly what i wish to do; and to you Round thank-you very much i will attempt this tonight.

Much appreciated, my knowledge regarding php is very limited at the moment I'm looking to expand.

Thanks

James. :D

wulfgar · December 21, 2006

Hmm seems i get this fatal error :S, im currently utilizing apache and winXP with the odbc driver.

Call to undefined function: odbc_fetch_array()

Round · December 21, 2006

Put an error msg in to see if the query fails

$rs = odbc_exec($conn,$query);
if( !$rs )
{
exit ("Could not exe query");
}

you could also try it this way

#retrieve data
while ( $row = odbc_fetch_array( $rs ) )
{
$list = "<table>";
$list .= "<tr>";
$list .= "<td class=table>".$row["Nfirst"]."</td>";
$list .= "<td class=table>".$row["nlast"]."<td>";
$list .= "<td class=table>
<form action=\"studentDetails.php\" method=\"post\"><input type=\"hidden\" name=\"Nfirst\" value=\"".$row["Nfirst"]."\"><input type=\"hidden\" name=\"Nlast\" value=\"".$row["Nlast"]."\"><input type=\"submit\" value=\"View\"></form></td>";
$list .= "</tr>";
$list .= "</table>";

#list details
echo( $list );
}

wulfgar · December 21, 2006

Hmm i still cant get it functioning, basically i can display all of the valid information on the same page as the view button (which is where the variables are located); anytime i try and script it in order to relay the variables to the viewDetail.php page i get an error concerning the odbc function ::

odbc_fetch_array();

very frustrating.

After searching over the internet i have discovered sessions is a good way in order to relay variables from script to script, would you recommend this for my current situation? I would greatly appreciate your insight :D

thanks round,

James.

Round · December 21, 2006

you can choose to use cookies or sessions depends on site use really. In my opinion cookies are easier to code and use.

just to check you have put this on the viewDetails.php page?
$Nfirst = $_POST['Nfirst']
$Nlast = $_POST['Nlast']

and then used $Nfirst and $Nlast to query the db again?

post your viewdatails script

wulfgar · December 21, 2006

Ok lets recap.

Here is my current initial script (containing the partial script you kindly provided):

//Variable stores textbox string
$FName = $_REQUEST['FName'];
$LName = $_REQUEST['LName'];

//connect to specified database (including username/pass for secure data)
$conn = odbc_connect('student_db', '', '') or die("<br/><b>Database Initialisation failed</b>");

//Search query
$query = "SELECT * from stu_db WHERE F_Name LIKE '$FName%' AND L_Name LIKE '$LName%'";

//execute connection to database
$rs = odbc_exec($conn,$query);

while(odbc_fetch_row($rs))
{
//variables store search results, second parameter is actual database column names
$NFirst = odbc_result($rs,"F_Name");
$NLast = odbc_result($rs,"L_Name");

//Delete query
//$delQuery = "SELECT from stu_db WHERE F_Name = '$NFirst' AND L_Name = '$NLast'";

//display first and last name of students from database in neat table; append view|edit|delete buttons to table rows.
echo "<html>
<head>
<link href=\"stylesheet.css\" rel=\"stylesheet\" type=\"text/css\">
</head>
<body>
<table border=\"0\" class=\"tablex\" width=\"400px\" cellpadding=\"0\">
<tr><td><b>$NLast</b>, $NFirst</td>
<td align=\"right\" valign=\"top\" width=\"10\">

//This is my view button, which is useless with 'rounds' script.

/*<form name=\"view\" method=\"post\" action=\"viewDetail.php\">
<input type=\"submit\" value=\"View\" class=\"button\">
</form>*/

<td align=\"right\" valign=\"top\" width=\"10\"><form name=\"edit\" method=\"post\" action=\"viewEdit.php\">
<input type=\"submit\" value=\"Edit\" class=\"button\">
</form></td>
<td align=\"right\" valign=\"top\" width=\"10\"><form name=\"delete\" method=\"post\" action=\"<?$rs2 = odbc_exec($conn,$delQuery);?>\">
<input type=\"submit\" value=\"Delete\" class=\"button\">
</form></td>
</td></tr>";

//variable transfer to viewDetail.php ('rounds' script)

#retrieve data
while ( $row = odbc_fetch_array( $rs ) )
{
$list = "<table>";
$list .= "<tr>";
$list .= "<td class=table>".$row["NFirst"]."</td>";
$list .= "<td class=table>".$row["NLast"]."<td>";
$list .= "<td class=table>
<form action=\"viewDetail.php\" method=\"post\">
<input type=\"hidden\" name=\"NFirst\" value=\"".$row["NFirst"]."\">
<input type=\"hidden\" name=\"NLast\" value=\"".$row["NLast"]."\">
<input type=\"submit\" value=\"View\">
</form></td>";
$list .= "</tr>";
}
$list .= "</table>";

#list details
echo( $list );
}

Now here is the code in my viewDetail.php script ::

<?

$NFirst = $_POST['NFirst'];
$NLast = $_POST['NLast'];

$conn = odbc_connect('student_db', '', '') or die("<br/><b>Database Initialization failed</b>");

$queryView = "SELECT * from stu_db WHERE F_Name = '$NFirst' AND L_Name = '$NLast'";

echo "$queryView"; //test

$rs2 = odbc_exec($conn,$queryView);

odbc_result_all($rs2);

?>

When i test to see the variables being passed to the query they are just blank.

I have been getting confused with the way in which you named the variables within your script, I am unsure on whether they should be the same variable names as my names within my initial while loop, or the variable names in which you provided (although i had assumed they should all be the same throughout the entire script).

Thanks :D

James.

Round · December 21, 2006

//Variable stores textbox string
$FName = $_REQUEST['FName'];
$LName = $_REQUEST['LName'];

//connect to specified database (including username/pass for secure data)
$conn = odbc_connect('student_db', '', '') or die("
Database Initialisation failed");

#Search query
$query = "SELECT * from stu_db WHERE F_Name LIKE '$FName%' AND L_Name LIKE '$LName%'";

#execute connection to database
$rs = odbc_exec($conn,$query);

#retrieve data
while ( $row = odbc_fetch_array( $rs ) )
{
$list = "<table>";
$list .= "<tr>";
$list .= "<td class=table>".$row["NFirst"]."</td>";
$list .= "<td class=table>".$row["NLast"]."<td>";
$list .= "<td class=table>
<form action=\"viewDetail.php\" method=\"post\">
<input type=\"hidden\" name=\"NFirst\" value=\"".$row["NFirst"]."\">
<input type=\"hidden\" name=\"NLast\" value=\"".$row["NLast"]."\">
<input type=\"submit\" value=\"View\">
</form></td>";
$list .= "</tr>";
$list .= "</table>";

#list details
echo( $list );
}
If this doesn't work bring the echo ($list) to outside the }

Then just to check they are passing, just have this on viewDetail.php
<?

$NFirst = $_POST['NFirst'];
$NLast = $_POST['NLast'];

echo($NFirst .$NLast)

?>

wulfgar · December 21, 2006

LOL, i have no idea why it is doing this ??? ???

Fatal error: Call to undefined function: odbc_fetch_array() in c:\apache\htdocs\db_connection.php on line 43

it just doesn't like that function within the odbc driver.

soo frustrating.

Round · December 21, 2006

LOL that is wierd I've tried this.

try rplacing array for row, trouble is if you have to j smiths and someone searches for him it wont like it cos it will need an array

//Variable stores textbox string
$FName = $_REQUEST['FName'];
$LName = $_REQUEST['LName'];

//connect to specified database (including username/pass for secure data)
$conn = odbc_connect('student_db', '', '') or die("
Database Initialisation failed");

#Search query
$query = "SELECT * from stu_db WHERE F_Name LIKE '$FName%' AND L_Name LIKE '$LName%'";

#execute connection to database
$rs = odbc_exec($conn,$query);

#retrieve data
while ( $row = odbc_fetch_row( $rs ) )
{
$list = "<table>";
$list .= "<tr>";
$list .= "<td class=table>".$row["NFirst"]."</td>";
$list .= "<td class=table>".$row["NLast"]."<td>";
$list .= "<td class=table>
<form action=\"viewDetail.php\" method=\"post\">
<input type=\"hidden\" name=\"NFirst\" value=\"".$row["NFirst"]."\">
<input type=\"hidden\" name=\"NLast\" value=\"".$row["NLast"]."\">
<input type=\"submit\" value=\"View\">
</form></td>";
$list .= "</tr>";
$list .= "</table>";

#list details
echo( $list );
}

Round · December 21, 2006

is it displaying the tables with the persons deatils in with a view button next to their name though??

and when you press view you get this error??

tracy · December 21, 2006

It sounds like the issue is with connection, no? Are you running this on your computer to test instead of a live server? Unless there is db connection successful, there will be no proper code function. I find that the connection verification right up front is my typical first issue. Once that is overcome, the code specific functions otherwise can be addressed. My opinion only...

[quote author=wulfgar link=topic=119385.msg489546#msg489546 date=1166701868]
LOL, i have no idea why it is doing this ??? ???

Fatal error: Call to undefined function: odbc_fetch_array() in c:\apache\htdocs\db_connection.php on line 43

it just doesn't like that function within the odbc driver.

soo frustrating.

[/quote]

wulfgar · December 22, 2006

I'm running it directly from my computer, although i am using apache and mySQL connections, so i just connect to my http://localhost/..

I'm sure there is no problem with my connection, the database is accessible and usable through my localhost.

Tonight i will attempt the use of 'row' instead Round, thanks so much for your valuable input, Ill keep you posted.

James.

wulfgar · December 22, 2006

Ok, so i thought i would quickly give it a show now, basically what i have now is the view button being displayed correctly, although when i click the view button i cant seem to get the viewDetail.php script to show me the variable's values with the simple echo.

Here is my viewDetail.php script:

<?
$NFirst = $_POST['NFirst'];
$NLast = $_POST['NLast'];

echo($NFirst .$NLast)
?>

Round · December 22, 2006

If the db you are using is mysql and is stored locally I would just use the normal mysql connection and not bother with an odbc connection:-

#connect to db
$conn = @mysql_connect( "localhost", "username", "password" )
or die( "Err:conn");

#create queries
$sql = "select * from table where column_name=\"$variable\" ";

#exe queries
$rs = mysql_query( $sql, $conn )
or die( "Could not exe query");

#retrieve data
while ( $row = mysql_fetch_array( $rs ) )
{
$list .= "<tr>";
$list .= "<td>".$row["coulumn"]."</td>";
$list .= "<td>".$row["coulumn2"]."</td>";
$list .= "<td>".$row["coulumn3"]."</td>";
$list .= "<td>".$row["coulumn4"]."</td>";
$list .= "</tr>";
}
$list .= "</table>";

echo( $list . "<br>" );
}

hope this helps

trq · December 22, 2006

[quote]If the db you are using is mysql and is stored locally[/quote]

Um... did you read the question?

Round · December 22, 2006

Um... Yes. he says he's using an access db in the first post and then there is mention of mysql so i was just trying to clarify a few things

wulfgar · December 23, 2006

Yeah sorry about that discrepancy, basically I'm using my computer as the server for apache at this time, unfortunately it is running windows XP, so in-order for PHP to work on the windows OS it requires the winMySQL to be active for some reason.. thats why i mentioned it, although i am using an access database.

The winMySQL is just an application.

Sorry about that if i confused you guys.

Cheers

James. ;D

wulfgar · December 23, 2006

Ok i think i might have made a little bit of progress.

I started looking at sessions etc, which inevitably moved me along to find 'header'.

So for my view detail button within the form i have placed this ::

<form name=\"view\" method=\"post\" action=\"<? header('Location: http://localhost/viewDetail.php?FN=$NFirst&LN=$Nlast'); ?>\"
<input type=\"submit\" value=\"View\" class=\"button\">
</form>

I can see this code sending the values of the variables in the URL address bar, except it has generated a further error for myself >:(

Now, when i click the view button i get this error ::

<b>Forbidden</b>
You don't have permission to access /< on this server

I want to cry.

James.

trq · December 23, 2006

Sorry, but that last post is way off track.

You would want something like...

[code]
<form method="post" action="example.php">
<input type="hidden" name="firstname" value="<?php echo $NFirst ?>">
<input type="hidden" name="lastname" value="<?php echo $Nlast ?>">
<input type="submit" value="View" class="button">
</form>
[/code]

Then, in example.php $NFirst and $NLast will be in $_POST['firstname'] and $_POST['lastname'].

wulfgar · December 23, 2006

Thanks Thorpe, but to my disappointment it still ceases to output the values of the variables here is what i have ::

[code]while($row = odbc_fetch_row($rs))
{
//variables store search results, second parameter is actual database coloumn names
$NFirst = odbc_result($rs,"F_Name");
$NLast = odbc_result($rs,"L_Name");

echo "<html>
<head>
<link href=\"stylesheet.css\" rel=\"stylesheet\" type=\"text/css\">
</head>
<body>
<table border=\"0\" class=\"tablex\" width=\"400px\" cellpadding=\"0\">
<tr><td><b>$NLast</b>, $NFirst</td>
<td align=\"right\" valign=\"top\" width=\"10\">
<form method=\"post\" action=\"viewDetail.php\">
<input type=\"hidden\" name=\"firstname\" value=\"<? php echo $NFirst ?>\">
<input type=\"hidden\" name=\"lastname\" value=\"<? php echo $Nlast ?>\">
<input type=\"submit\" value=\"View\" class=\"button\">
</form>
<td align=\"right\" valign=\"top\" width=\"10\">
<form name=\"edit\" method=\"post\" action=\"viewEdit.php\">
<input type=\"submit\" value=\"Edit\" class=\"button\">
</form></td>
<td align=\"right\" valign=\"top\" width=\"10\">
<form name=\"delete\" method=\"post\" action=\"<?$rs2 = odbc_exec($conn,$delQuery);?>\">
<input type=\"submit\" value=\"Delete\" class=\"button\">
</form></td>
</td></tr>";
}
[/code]

And here is the viewDetail.php file

[code]<?

$FirstName = $_POST['firstname'];
$LastName = $_POST['lastname'];

echo "$FirstName";
?>[/code]

All it shows is a blank page, even with the echo's.

trq · December 23, 2006

There shouldn't be a space between <? and php. eg;

[code=php:0]
<? php echo $NFirst ?>
[/code]

should be...

[code=php:0]
<?php echo $NFirst ?>
[/code]

wulfgar · December 23, 2006

Nope.. no difference still just displays a blank page.

Shouldn't there be a very easy solution to what i am trying to achieve here.

All i require is that the variables $NFirst and $NLast values can be passed from my first script (where the variables are declared and where they obtain their values) to the second script (where i will utilize their values in an alternate query.

And these values are to be passed when i click a button 'View Details'.

Thanks for your input Thorpe.

James.

Sign In

[SOLVED] Help -- PHP and ODBC Database application

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information