Jump to content

Help with select/where clause using


crmamx

Recommended Posts

I am brand new to mysql and php but I have created a database and loaded two tables using cPanel and phpMyAdmin. Now I need some programs to access my data. I have a couple of simple ones that work, but I can't figure out what I really need,

 

I am trying to Select a table Where the Value is a $variable, not a fixed value.

 

Of course the end result will be to pass the value from a Form, but I have to get this to work first.

 

 

 

<?php

// Connect to database=============================
include("connect_db.php"); 
$table='airplanes';
$amano='123456'
$iden='1'

// Send query ===========================================================
// $result = mysql_query("SELECT * FROM {$table} where ama='123456'"); == this works
// $result = mysql_query("SELECT * FROM {$table} where ama='940276'"); == this works
// $result = mysql_query("SELECT * FROM {$table} where id='1'"); // this works
// $result = mysql_query("SELECT * FROM {$table} where id = '{$iden}'"); == doesnt work
// $result = mysql_query("SELECT * FROM {$table} where id = $iden"); == doesnt work
// $result = mysql_query("SELECT * FROM {$table} where id = ($iden)"); // == doesnt work
// $result = mysql_query("SELECT * FROM {$table} where id = $iden"); // == doesnt work
// $result = mysql_query("SELECT * FROM {$table} where ama='$amano'"); // == doesnt work
$result = mysql_query("SELECT * FROM {$table} where ama=($amano)"); // == doesnt work

 

Thanks

 

 

Link to comment
Share on other sites

Start by removing the query string from the query execution and storing it in a variable, then use the variable in the query execution. Check to make sure the query executes successfully, and if not, echo the error along with the query string.

 

$query = "SELECT `field` FROM `table` WHERE `some_field` = 'some_value'";
if( $result = mysql_query($query) ) {
     if( mysql_num_rows($result) > 0 ) {
          echo 'Query ran successfully and returned ' . mysql_num_rows($result) . 'results.';
     } else {
          echo 'Query ran successfully, but returned an empty result set';
     }
} else {
     // query failed to execute
     echo "<br>Query: $query<br>Produced error: " . mysql_error() . '<br>';
}

Link to comment
Share on other sites

Thanks for the reply.

 

$table='airplanes';
$amano='123456'
$result = mysql_query("SELECT * FROM {$table} where ama={$amano}"); // == doesnt work

 

Getting ready to try it but might I ask why doesn't $iden or $amano work with the Where clause? $table works fine with the From clause.

Link to comment
Share on other sites

I guess I just do not understand. Tried this:

<?php
include("connect_db.php");
$amano='123456'

$query = "SELECT `ama` FROM `airplanes` WHERE `ama` = '$amano'"; // doesn't work
// $query = "SELECT `ama` FROM `airplanes` WHERE `ama` = '123456'"; // works, returns 1 result
// $query = "SELECT `field` FROM `table` WHERE `some_field` = 'some_value'";

Link to comment
Share on other sites

<html>
<head>
<title>MySQL Table Viewer
</title>
</head>
<body>
<?php
// Connect to database=============================

include("connect_db.php");
$amano='123456'

$query = "SELECT `ama` FROM `airplanes` WHERE `ama` = '$amano'"; // does't work
// $query = "SELECT `ama` FROM `airplanes` WHERE `ama` = '123456'"; // works, returns 1 result
// $query = "SELECT `field` FROM `table` WHERE `some_field` = 'some_value'";
if( $result = mysql_query($query) ) {
if( mysql_num_rows($result) > 0 ) {
echo 'Query ran successfully and returned ' . mysql_num_rows($result) . 'results.';
} else {
echo 'Query ran successfully, but returned an empty result set';
}
} else {
// query failed to execute
echo "<br>Query: $query<br>Produced error: " . mysql_error() . '<br>';
}

?>
</body>
</html>

Link to comment
Share on other sites

I can't believe it. I have pulled my hair out. I am an old time cobol/rpg/fortran programmer. Just a novice with HTML and javascript but I usually can get it to work. I know the kind of things to look for but this one has stumped me......till you posted this:

 

Parse error:  You forgot your semi-colon after your $amano variable declaration.

 

It works fine. Now I will try to pass the variable to php from a Form.

 

Thank you so much. I have tried several forums but this is the first one where the participants are serious rather than flaming a novice.

 

Link to comment
Share on other sites

As long as people ask a reasonable question, and at least try to help themselves, we try to help them learn :)

 

Which brings up the point that you will save yourself a ton of headaches if you develop with error reporting at max, and display errors on. If you need to know how to do that, just say so.

Link to comment
Share on other sites

Pikachu:

 

Finally after hours and hours I got the basics to working like I want it. Never would have got it without your help. Now I need to do some error checking and other stuff.

 

Would you be interested in  reviewing it and offering your expert comments on what I have done?

 

Thanks a bunch!

Link to comment
Share on other sites

This is what I ended up with and it seems to work well.

 

<html>
<head>
</head>
<body>
<?php
// Connect to database=====================================================

include("connect_db.php"); 
$table='airplanes';

// retrieve form data ======================================================

$amano = $_POST['amano']; 

// sending query ===========================================================

$result = mysql_query("SELECT * FROM $table
WHERE ama='$amano'") or die(mysql_error());  

if (!$result) {
    die("Query to show fields from table failed");
}

echo "<table border='10' cellpadding='3' cellspacing='2'>";
echo "<p>Airplanes for Joe Blow</p><br>";

echo "<tr> <th>ID</th> <th>AMA #</th> <th>Model Name</th> <th>Model MFG</th><th>Wingspan</th><th>Engine</th><th>Decibels</th></tr>";

// keeps getting the next row until there are no more to get ================

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

// Print out the contents of each row into a table ==========================

echo "<tr><td>";
echo $row['id'];
echo "</td><td>"; 
echo $row['ama'];
echo "</td><td>"; 
echo $row['model_name'];
echo "</td><td>"; 
echo $row['model_mfg'];
echo "</td><td>"; 
echo $row['wingspan'];
echo "</td><td>"; 
echo $row['engine'];
echo "</td><td>"; 
echo $row['decibels'];
echo "</td></tr>"; 
} 

echo "</table>";
?>
<br>
Put something here.
<body>
</html>

 

This program will display all the entries for the AMA Number entered. Now I need 3 more programs:

 

1. Delete an ID number.

2. Add a new entry.

3. Update an ID number. Suspect this will be the most difficult.

 

This stuff is addictive. I need to go work on my airplanes.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.