INNER JOIN QUERY ? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

INNER JOIN QUERY ?

Author

Topic

ciccicci
Starting Member

3 Posts

Posted - 2004-05-23 : 15:02:19

I'm trying to complete a little exercise I have got from a course I'm comleting in Remote Access database and I'm trying to build the code in PHP SQL to display all the prints which belong to an artist the user can select
I have written this code but it doesn't work, can someone help?
the first cide is to create the form
<HTML>
<HEAD>
<TITLE> PHP Project</TITLE>
</HEAD>

<BODY>
<?php
$dsn = "ecommerce";
$user = "";
$upasswd = "";
$conn = odbc_connect($dsn,$user,$upasswd);
if($conn <= 0) {
echo "Error in connection<BR>";
exit;
}

echo('<P><CENTER><H1>My Beautiful Shop<H1></CENTER><P>');

//Build and submit a query:
$query = "SELECT artist_id, artist_name FROM artists";
$result = odbc_exec($conn, $query);

//Let's create a standard HTML form....
echo('<FORM METHOD=POST ACTION="getPrints.php">');

//Start a SELECT box
echo("<SELECT NAME='artists'>");

//Fetch each row in the result table...
//..we'll use a loop with odbc_fetch_row($result) to select each row
//and odbc_result($result, "attribute") to fetch individual attributes in the row
while(odbc_fetch_row($result)){
$artist_id = odbc_result( $result,"artist_id");
$artist_name = odbc_result( $result,"artist_name");
//...and display each row as an OPTION
echo("<OPTION value=\"$artist_id\">$artist_name</OPTION>");
}

//Finish the SELECT statement
echo('</SELECT>');
echo('<P>Fetch all prints taken by selected artist<P>');
//Don't forget to include a SUBMIT button
echo('<INPUT TYPE="submit">');

//End the FORM
echo('</FORM>');

//Close connection:
odbc_close($conn);

//LINK back to home page
echo("<A HREF='index.php'>Home Page</A>");
?><br>

</BODY>
</HTML>

this second is to create the action script
<HTML>
<HEAD>
<TITLE>New Document </TITLE>
</HEAD>
<BODY>

<?php

//check form for values...
function checkString($string)
{
//check argument to ensure that it contains a0 something and b)something more than whitespace
if ($string && ( ($string != '\n') && ($string != ' ') && ($string != '\t') ) ) {
//everything OK? then return 0
return 0;
}
else {
//problem - return 1
return 1;
}
}
//check for errors...
//We'll use the string $errors to build up our message to send back to the user
//in case of problems
$errors ='';
$artist_name = $_POST["artist_name"];
$telephone = $_POST["telephone"];
//call our function checkString() with one of the return values from calling form
if(checkString($artist_name)){
//remember we return 1 from the function on error. In the context of an if() statement
//this will be interpreted as true, so add our error string
$errors .=' Artist Phone Number<BR>';
}
//Do we have any errors?
if($errors){
//if so, output our error message....
echo("Please supply values for the following fields<P>$errors");
//and quit script
exit;
}
//we only reach here if everything is OK
//Same routine as before
$dsn ="ecommerce";
$user = "";
$upasswd = "";
$conn = odbc_connect($dsn,$user,$upasswd);
if($conn <= 0) {
echo "error in connection<BR>";
exit;
}
//... we would probably use an Autonumber field in reality to save this next bit of code,
//but these exercises ae all about learning PHP not MS Access
//We'll need to calculate the next value for the primary key AuID - so let's
//use SELECT statement to find the highest current value in the table, and then add 1
$query = "SELECT artists.artist_id FROM artists ORDER BY artists.artist_id DESC;";
$result = odbc_exec($conn, $query);
//calculate the next AuID value
$artist_id = odbc_result($result,"artist_id")+1;
//We're now ready to insert a new record
$query = "INSERT INTO artists ( artist_id, artist_name, telephone) VALUES ('$artist_id','$artist_name','$telephone');";
//This time we'll check the return value from odbc_exec() to see if we were successful
$result = odbc_exec($conn, $query);
//was it successful?
if($result){
echo ('New artist record added');
}
else {
echo ('New artist record could not be added');
}
//LINK back to home page
echo("<P><A HREF='index.php'>Home Page</A>");
//Close connection:
odbc_close($conn);
?>
</BODY>
</HTML>

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-05-23 : 18:39:42

ciccicci,

This is a SQL Server forum. Our advice to you is going to be "create a stored procedure and call that from your PHP code" because that's the right thing to do. If you want advice on PHP you might get lucky here, but you really should look up a PHP forum. You could also try the PHP category at www.dbforums.com.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources