Page 1 of 1

[SOLVED] Trying to understand option selection logic

Posted: Mon Jun 09, 2008 3:04 pm
by texmansru47
I have a query form where I allow the user to select a model of product and then from their selection it will run a query on a php page to find the data. But I guess I need to understand the basics first. In my example I have the following query that will do the work with the user's data:

Code: Select all

$copy = mysql_fetch_array(mysql_query("SELECT * FROM `snatest` WHERE `OEMSku`= '$_POST[OEMSku]'")) or die(mysql_error()); 
 
I have all the proper open and access data stuff prior to this.

On the main form the code looks like this:

Code: Select all

<body bgcolor="#C0C0C0">
 
<h1> <img border="0" src="logo1.jpg" width="78" height="77">&nbsp;
<font face="Arial" color="#000080">Lookup Process</font> </h1>
<form id="form1"; name="form1"; method="post" action="lookup.php">
 <table width="366"; border="0"; cellspacing="0"; cellpadding="4";>
   <tr>
   <td align="left" valign="top" width="94"><b>
    <font face="Arial" color="#000080" size="2">OEM SKU:</font></b></td>
   <td align="left" valign="top" width="224"><font color="#000080" face="Arial">
    <font color="#000080"><select name="OEMSku" style="font-weight: 700">
     <option value="1";>Test1</option>
     <option value="2";>Test2</option>
     <option value="3";>Test3</option>
     <option value="4";>Test4</option>
    </select></font><b><font size="2"> </font></b></font>
   </td>
  </tr>
  <tr>
   <td ; align="right"><input type="submit" name="Submit" value="Submit" />
   </td>
  </tr>
 </table>
</form>
 
When I break out the code it appears that the user data they select is being ignored, since I get no results for the query to run to product data.

So what I need to understand is what should I call in this instance... the $_POST[OEMSku] or the variables the users are selecting? I have tried several attempts at trying to figure out the logic but I guess I'm not approaching this correctly.

Ideas?

Texman

Re: Trying to understand option selection logic

Posted: Mon Jun 09, 2008 3:59 pm
by timsewell
In your SQL query you have OEMSku in backticks, where it should be in single quotes. Not sure if that's the cause of the problem, but I do believe it may be incorrect syntax.

Edit: In fact, once you've checked that it is indeed an integer value, as you are expecting, I seem to recall that it doesn't need quotes either, being numeric rather than a string.

Re: Trying to understand option selection logic

Posted: Mon Jun 09, 2008 4:57 pm
by dbemowsk
timswell, that is incorrect. the field name should either be with backticks or nothing. "OEMSku = '" or "`OEMSku` = '" should be used, so his original syntax is correct. Also, it is usually suggested to use "$_POST['OEMSku']" instead of "$_POST[OEMSku]".
texmansru47 wrote:$copy = mysql_fetch_array(mysql_query("SELECT * FROM `snatest` WHERE `OEMSku`= '$_POST[OEMSku]'")) or die(mysql_error());
I do not recommend packing all of these functions into one line. Here is what I would do with this.

Code: Select all

 
$sql = "SELECT * FROM `snatest` WHERE `OEMSku`= '$_POST['OEMSku']'";
echo $sql; // comment this or remove it after your query is working.
$results = mysql_query($sql);
if ($results) { //this will check if the query failed or not
 if (mysql_num_rows($results) > 0) {  //this will check if results were returned
    while($copy = mysql_fetch_array($results) {
      [your code]
    }
  } else {
    echo "No results returned";
  }
} else {
  echo "Query error: ".mysql_error();
}
 
With this you can see what is actually going on. if the query finds no results, you will see that and know to check your query or table of data to see what's up. If you see the "Query error: ..." you will be able to sort out your query. And echoing out the $sql will let you see the query with all values inserted to see if there is some issue with the format of your query.

Hope that helps.

Re: Trying to understand option selection logic

Posted: Mon Jun 09, 2008 5:05 pm
by hansford
try this:
$oemsku = $_POST['OEMSku'];
$query = "SELECT * FROM snatest WHERE OEMSku = '$oemsku'";
$copy = mysql_fetch_array(mysql_query($query))or die(mysql_error());

Re: Trying to understand option selection logic

Posted: Mon Jun 09, 2008 9:16 pm
by texmansru47
I do not recommend packing all of these functions into one line. Here is what I would do with this.
I will try that. I appreciate your assistance.

Thanks,

Texman

Re: Trying to understand option selection logic

Posted: Mon Jun 09, 2008 9:37 pm
by texmansru47
Well in a practice attempt I ran your code and I got it working (the query was looking for the correct data from the input) but I get a blank results screen again. So the logic I needed to understand I get, but I guess I do have my sql query out of whack or something since it is not listing the results. This is what I have:

Code: Select all

$sql = "SELECT * FROM `snatest` WHERE `OEMSku`= '$_POST[OEMSku]'";
// echo $sql; // comment this or remove it after your query is working.
$results = mysql_query($sql);
if ($results) { //this will check if the query failed or not
 if (mysql_num_rows($results) > 0) {  //this will check if results were returned
    while($row = mysql_fetch_array($results));
     {
        $variable1=$row["BatchNum"];
        $variable2=$row["OEMSku"];
        $variable3=$row["CardType"];
        $variable4=$row["OEMModel"];
        $variable5=$row["Qty"];
        $variable6=$row["ModelType"];
        $variable7=$row["RecvDate"];
        //table layout for results
 
        print ("<tr>");
        print ("<td>$variable1</td>");
        print ("<td>$variable2</td>");
        print ("<td>$variable3</td>");
        print ("<td>$variable4</td>");
        print ("<td>$variable5</td>");
        print ("<td>$variable6</td>");
        print ("<td>$variable7</td>");
        print ("</tr>");
        echo "All Batches Listed";
    }
  } else {
    echo "No results returned";
  }
} else {
  echo "Query error: ".mysql_error();
}
 

Re: Trying to understand option selection logic

Posted: Mon Jun 09, 2008 9:46 pm
by hansford
and again you have the query string wrong. Look at the previous examples.

Re: Trying to understand option selection logic

Posted: Mon Jun 09, 2008 10:25 pm
by texmansru47
Yes that code works but I cannot get the array to pull all the data required... i.e. for one example I have three entries in my table and this code only pulls the first one:

Code: Select all

$oemsku = $_POST['OEMSku'];
$query = "SELECT * FROM snatest WHERE OEMSku = '$oemsku'";
$copy = mysql_fetch_array(mysql_query($query))or die(mysql_error());
 
I'm sure I need a if statment that will loop but I cannot get that to work either.

Re: Trying to understand option selection logic

Posted: Mon Jun 09, 2008 10:36 pm
by texmansru47
You see:

Code: Select all

$oemsku = $_POST['OEMSku'];
$query = "SELECT * FROM snatest WHERE OEMSku = '$oemsku'";
$copy = mysql_fetch_array(mysql_query($query))or die(mysql_error());
 
while ($row = @mysql_fetch_array($copy))
{
        $variable1=$row['BatchNum'];
        $variable2=$row['OEMSku'];
        $variable3=$row['CardType'];
        $variable4=$row['OEMModel'];
        $variable5=$row['Qty'];
        $variable6=$row['ModelType'];
        $variable7=$row['RecvDate'];
        //table layout for results
 
        print ("<tr>");
        print ("<td>$variable1</td>");
        print ("<td>$variable2</td>");
        print ("<td>$variable3</td>");
        print ("<td>$variable4</td>");
        print ("<td>$variable5</td>");
        print ("<td>$variable6</td>");
        print ("<td>$variable7</td>");
        print ("</tr>");
        echo "All Batches Listed";
        }
//below this is the function for no record!!
if (!$variable1)
{
print ("No Records where pulled.");
}
else                     
{
echo "All Batches for your selected model are Listed";
}
 
If I remove the where statement, the first record is the only one is pulled. When I add it, I get nothing.

Re: Trying to understand option selection logic

Posted: Tue Jun 10, 2008 5:12 am
by dbemowsk
You do not have this coded the way I told you to.

Code: Select all

 
$copy = [color=#FF0000]mysql_fetch_array[/color](mysql_query($query))or die(mysql_error());
 
while ($row = @[color=#FF0000]mysql_fetch_array[/color]($copy))
 
You have mysql_fetch_array in there twice. The second one you are supressing errors on with the @ symbol before it. This is completely wrong. Follow my previous post on how to code this.

If you are not getting the results with the where clause in there then you need to check what is getting inserted into your where clause for "$_POST['OEMSku']". That is why I had you print/echo out the $sql so that you can check the query with your database table. See if the value that gets inserted from"$_POST['OEMSku']" into the query is even a value in your table. I would bet a million bucks that it isn't which is why it is not returning any results with the where clause inserted.

Follow your query logically and see what is happening. If you see

Code: Select all

SELECT * FROM snatest WHERE OEMSku = ''
printed out when running my code then you have an issue with how your "$_POST['OEMSku']" is getting to the script from your form.If you see

Code: Select all

SELECT * FROM snatest WHERE OEMSku = '12345'
printed out and you get no results, then 12345 is not an OEMSku in your table.

You said previously that my code worked but there were still no results returned and you figured that it was your SQL query that was the problem. This was the correct thinking. Follow that logic and find out what is wrong with your query. The solution is not to revert back to your bad code.

Logical thinking is a big key.

Re: Trying to understand option selection logic

Posted: Tue Jun 10, 2008 9:45 pm
by texmansru47
The problem is I ran your code exactly to try it out. I had to add a few syntax resolutions here and there to get the errors to go away. When it ran the echo $sql showed me that proper entry for the what I inputted into the form. The problem is that the final results was empty. I got my headers but no data.

I ran the other guy's code to see what it produced and I got the first of three records from my database... it just would not run the array to list all the findings.

What I have now is a hybrid that does not work well. I will start over on a blank page to see what I can get from your code again. Since I'm not a developer by trade and I'm having to learn this day by day I appreciate your input.

Thanks,

Texman

Re: Trying to understand option selection logic

Posted: Tue Jun 10, 2008 10:11 pm
by donnatravels
you make my day 8)

Re: Trying to understand option selection logic

Posted: Tue Jun 10, 2008 10:16 pm
by texmansru47
This is the code I have in place on the new script (I have not included the attach to the database and server parts).

Code: Select all

$sql = "SELECT * FROM `snabatch` WHERE `OEMSku`= '$_POST[OEMSku]'";
echo $sql; // comment this or remove it after your query is working.
$results = mysql_query($sql);
if ($results) { //this will check if the query failed or not
 if (mysql_num_rows($results) > 0) {  //this will check if results were returned
    while($copy = mysql_fetch_array($results)) {
        $variable1=$copy['BatchNum'];
        $variable2=$copy['OEMSku'];
        $variable3=$copy['CardType'];
        $variable4=$copy['OEMModel'];
        $variable5=$copy['Qty'];
        $variable6=$copy['ModelType'];
        $variable7=$copy['RecvDate'];
        //table layout for results
 
        print ("<tr>");
        print ("<td>$variable1</td>");
        print ("<td>$variable2</td>");
        print ("<td>$variable3</td>");
        print ("<td>$variable4</td>");
        print ("<td>$variable5</td>");
        print ("<td>$variable6</td>");
        print ("<td>$variable7</td>");
        print ("</tr>");    }
  } else {
    echo "No results returned";
  }
} else {
  echo "Query error: ".mysql_error();
}
 
And it is working now. I reviewed everything I did as you mentioned and I think I understand now. I appreciate your help.

Thanks again,

Texman