Jump to content

help: selct from row


funbinod

Recommended Posts

am very fresh in php and whatever i learnt is from w3schools.

 

i am trying to create invoice script. for this i wish if automatic display of "item name" & "price" by entering just "item code" be possible.

 

there is a table for stock item with rows "item code", "item name", "price".

please guide me.

 

thank u.

Edited by funbinod
Link to comment
Share on other sites

If you have the item code, then you can use a SQL statement to get the record from the database with that code, and display the rest of the information from the record. What part of that do you need help with, have you worked with databases? That's just a SELECT query with a WHERE clause to filter the records.

Link to comment
Share on other sites

thanks justsomeguy for ur kind reply. i've just begun working with databases. i've used SELECT & WHERE command that was taught on w3schools. but that taught selecting with one particular information. but i wish selecting from all rows.(m not sure how to explain either)

 

w3schools taught me selecting from a single row like -

 

<?php$con=mysqli_connect("example.com","peter","abc123","my_db");// Check connectionif (mysqli_connect_errno()) { echo "Failed to connect to MySQL: " . mysqli_connect_error(); }$result = mysqli_query($con,"SELECT * FROM PersonsWHERE FirstName='Peter'");while($row = mysqli_fetch_array($result)) { echo $row['FirstName'] . " " . $row['LastName']; echo "<br>"; }?>

 

it teaches if 'Peter' is selected as 'FirstName' then 'LastName' should be selected from same row. but i wonder if there is one thousand rows then should i have to write lines for each row? or there is alternative? or i misunderstood the code? please guide.

Link to comment
Share on other sites

It's the same code. You're looking for rows that match a certain item code instead of first name. That doesn't matter though, the database doesn't know or care what an "item code" or "first name" is, it's all just data. It says "SELECT *" because it is selecting the entire record. If it was only getting a few fields it would say "SELECT FirstName, LastName", but using * tells it to select everything. You can print the item name and price instead of the last name. Again, the database doesn't know or care what things like "item name", "price", "last name" etc. are, they're just pieces of data.

  • Like 1
Link to comment
Share on other sites

thank u again.

and again confusion continues. (sorry! its because am newbie in php or programming)

let me explain differently.

if 'peter' is selected as 'first name', then it echos his 'last name'.

i understood this line. but if i want to echo 'last name' of another name (lets say 'john') then should i have to write another code like

 

<?

$result = mysqli_query($con,"SELECT * FROM PersonsWHERE FirstName='John'");while($row = mysqli_fetch_array($result)) { echo $row['FirstName'] . " " . $row['LastName']; echo "<br>"; }

?>

 

and similar for each name!

 

or can i write a single line for each and every data!?

(if u'll feel comfortable seeing what i wrote, i would love to send u the file)

Link to comment
Share on other sites

That's what the query will have to be, yes. You don't need to write the literal query though, if the name you're searching for is in a variable then use the variable in the query to put the name they typed there.

$first_name = 'Peter'; // change that to whatever you want, or set it from a form or whatever else$stmt = mysqli_prepare($con, 'SELECT FirstName, LastName FROM Persons WHERE FirstName=?');mysqli_stmt_bind_param($stmt, 's', $first_name);mysqli_stmt_execute($stmt);mysqli_stmt_bind_result($stmt, $fname, $lname);while (mysqli_stmt_fetch($stmt)) {  echo $fname . ' ' . $lname . '<br>';}
http://www.php.net/manual/en/mysqli.prepare.php
Link to comment
Share on other sites

That's what the query will have to be, yes. You don't need to write the literal query though, if the name you're searching for is in a variable then use the variable in the query to put the name they typed there.

$first_name = 'Peter'; // change that to whatever you want, or set it from a form or whatever else$stmt = mysqli_prepare($con, 'SELECT FirstName, LastName FROM Persons WHERE FirstName=?');mysqli_stmt_bind_param($stmt, 's', $first_name);mysqli_stmt_execute($stmt);mysqli_stmt_bind_result($stmt, $fname, $lname);while (mysqli_stmt_fetch($stmt)) {  echo $fname . ' ' . $lname . '<br>';}
http://www.php.net/manual/en/mysqli.prepare.php

 

 

thank u! i think this is now on the way to my goal.

 

i tried ur suggestive code as my own like this -

 

<?$icode = '$_POST[icode]';$stmt = mysqli_prepare($con, 'SELECT icode, item FROM stock WHERE icode=$_POST[icode]');mysqli_stmt_bind_param($stmt, 's', $icode);mysqli_stmt_execute($stmt);mysqli_stmt_bind_result($stmt, $icode, $item);while (mysqli_stmt_fetch($stmt)) {  echo $item;}?>

buy it showed no result. please help finding problem.

 

thank u

Edited by funbinod
Link to comment
Share on other sites

When you put variables in single quotes like that, PHP does not replace the value. This is the exact query you are sending to MySQL:SELECT icode, item FROM stock WHERE icode=$_POST[icode]It is not using the value from $_POST['icode'], it is using that text.Look at my example again. The query does not contain the actual value to use, it contains a "?" placeholder. The bind_param function tells it what value to use for that placeholder. Look at the manual page for bind_param. The "s" passed to bind_param tells it that the value is a string (rather than an integer, or float, or whatever else). Use the placeholder in the query and then use bind_param to tell it what value to use for that placeholder.

Link to comment
Share on other sites

i'm sorry sir! but its completely confusing for me. i cant understand how is this made done. can u please, if u dont mind, write that code, for example, so that i can understand this more easily.

what i designed the invoice and wish it to do is like this---

 


<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>TEST CASHIER</title>
<link rel=stylesheet href=style.css?>
</head>
<body bgcolor="#999999">
<h1>Test Invoice</h1>
<h3>Customer Name: Laptop House</h3>
<table align="left" width="75%" cellspacing="10" border="1">
<tr>
<td width="50%" valign="top">
<table leftmargin=0 marginheight=0 marginwidth=0 topmargin=0 border=1 cellspacing=0 width="100%">
<form action="tempinvoice.php" method="POST" id="cart">
<tr>
<td width=120>Item Code: </td>
<td><input type=text name=icode style="width:900;"></td><!-- Item Code' is entered here-->
</tr>
<tr>
<td>Item Name: </td>
<td><input type=text name=item style="width:900" readonly="readonly"></td><!-- Item Name' is displayed here auto -->
</tr>
<tr>
<td>Rate:</td>
<td><input type=text name=rate style="width:900" readonly="readonly"></td><!-- & 'Rate' is displayed here auto -->
</tr>
<tr>
<td>Quantity:</td>
<td><input type=text name=qty style="width:900"></td>
</tr>
<tr>
<td>Discount (%):</td>
<td><input type=text name=less style="width:900"></td>
</tr>
<tr>
<td>Amount:</td>
<td><input type=text name=amt style="width:900"></td>
</tr>
<tr>
<td> </td>
<td ><input type=submit class="nav" value='Add to Cart'></td>
</tr>
<input type=hidden name=action value=doregister>
</form>
</table>
</td>
<td align="center"> </td> <!-- & every item added to cart is displayed here in a table (with customer name) which would be posted at once to the database -->
</tr>
</table>
</body>
</html>
Edited by funbinod
Link to comment
Share on other sites

What are you confused about? I already gave you an example. This is where the query is defined:

$stmt = mysqli_prepare($con, 'SELECT FirstName, LastName FROM Persons WHERE FirstName=?');
That has a query, and it has a "?" placeholder for a value. This line:
mysqli_stmt_bind_param($stmt, 's', $first_name);
Tells it to use the value of the variable $first_name in the query, and it tells it that this value is a string. If you had multiple values in the query, like this:
$stmt = mysqli_prepare($con, 'SELECT FirstName, LastName FROM Persons WHERE FirstName=? OR LastName=?');
Then you would bind multiple values:
mysqli_stmt_bind_param($stmt, 'ss', $first_name, $last_name);
That is telling it that both values are strings, and then it gives the variables to use.If you're confused then look this stuff up in the manual and read about it, that's what the manual is there for. The manual includes several examples and comments from other people. If you have questions about how to use various things then ask, that's why we're here, but I'm not going to write your code for you. I'm trying to help you learn, not do your work for you.http://www.php.net/manual/en/mysqli.prepare.phphttp://us1.php.net/manual/en/mysqli-stmt.bind-param.phphttp://us1.php.net/manual/en/mysqli-stmt.execute.phphttp://us1.php.net/manual/en/mysqli-stmt.bind-result.phphttp://us1.php.net/manual/en/mysqli-stmt.fetch.php
  • Like 1
Link to comment
Share on other sites

sorry sir! i didnt meen to make u do my work. actually i'm not doing any work. m not in any mission. i just imagined something and tried to do it. but i forgot that i'm nothing on coding. i've just started learning php and i tried on applications. i know almost nothing about php & mysql, that's why i stuck on every step i move on. since i'm very new on coding, it was bitta difficult for me to understand, so i asked u for direct help. i didnt imagine it will be kind of asking to do work in my favor.

 

but i think i got some idea from this reply and i can try my code now. i'll try and will be back.

 

one thing more before i move on - 'can this be done within a FORM?' i mean select 'where' clause from a form field and data be displayed on next form field.

 

thank u for ur kind support..

Edited by funbinod
Link to comment
Share on other sites

  • 3 weeks later...

m back again here. i'm trying to understand what u gave me on reference. now m understanding this a bit more. but m confused that this echos the first name or last name after "submitting" the form or only after "inputting" the value on form?

Link to comment
Share on other sites

I'm not sure what you're confused about. You can run a query at any time. When you create a form, when a form gets submitted, on a page without a form, whenever you want to do it. I'm not sure what you're trying to accomplish or are confused about.

Link to comment
Share on other sites

ok!

i describe it with what i tried to code. what i'm trying is in the comments within the code---

<form action="#" id="test" name="test"><input name="icode" id="icode" type="text"><br /> <!-- after inputing 'code' here --><?php$icode = '$_POST[icode]';$result = mysql_query("SELECT * FROM stockWHERE icode='$icode'");while($row = mysql_result($result))  {  echo '<input name="item" id="item" value="$row[item]" placeholder="i wish the ITEM NAME be displayed here automatically">';  }?><br /><!--i wish the 'item name' be displayed here automatically --><input name="qty" id="qty" type="text"><br /><input name="rate" id="rate" type="text"><br /><input name="amt" id="amt" type="text"><br /><input type="submit" name="submit" id="submit" value="SUBMIT"></form>

but it returned --

 

Warning: mysql_result() expects at least 2 parameters, 1 given in E:xampphtdocsactestjQuery.php on line 30

 

and please look for other errors or techniques....

Link to comment
Share on other sites

that error is pretty clear. mysql_result expects 2 parameters, and you are only giving it 1. So the next step would be to look up what mysql_result expects, and give it the correct parameters.

http://www.w3schools.com/php/func_mysql_result.asp

 

Also, this is wrong

$icode = '$_POST[icode]';

you are literally assigning the $icode a value of $_POST[icode]. What you need to do is assign it the value _of_ $_POST['icode']

 

Additionally, you aren't validating any of the user input. You can better protect yourself by dropping the mysql extension and using mysqli_ with prepared statements or PDO.

Link to comment
Share on other sites

the parameter for mysql_result() u meant (or PHP meant) might be with "mysql connection", i think. if so i've done this before the line. i dunno if it should be included there.

 

and i understood the mistake while assigning $icode.

 

additionally, i couldn't understand the validation u noticed on any of the user input. and i got little confused while using "mysqli" function. i couldnt understand that if it is just a replacement of "mysql" or there will be some additional changes in it... please guide....

Link to comment
Share on other sites

the parameter for mysql_result() u meant (or PHP meant) might be with "mysql connection", i think. if so i've done this before the line. i dunno if it should be included there.

I'm not sure what you mean. if you are going to call mysql_query, you have to use it correctly, and pass in the two required parameters. this must always happen.

 

 

 

additionally, i couldn't understand the validation u noticed on any of the user input. and i got little confused while using "mysqli" function. i couldnt understand that if it is just a replacement of "mysql" or there will be some additional changes in it... please guide....

Yes, it is a replacement for mysql. the i stands for improved. here are all the functions.

http://www.php.net/manual/en/mysqli.summary.php

 

sometimes there is a 1-1 mapping, but not always. a google search should help you find the appropriate mysqli substitute for any given mysql function.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...