Pull specific info from a database using PHP

[yellowlitegames]

I had a giant XML file. (Over 18 mg) and I have parsed that to a MySQL Database. In the database are about 1130 rows with about 50 columns. Each row is a different product that is specified by its product number. Each column after that holds info for that one product.

If someone could help me, I am trying to find a way to pull info just for one specific product. I also don't want the output in a table or anything. The info will be all over a webpage.

If anyone could help me that would be amazing. I have been searching all over the internet and I am on a very tight schedule and have not had any luck with tutorials or any other help sections.

Thanks alot.

[kiwibrit]

I'm not quite clear what you are asking. From what you say, each row has the SKU and descriptions such as name, length, and so on. If you wanted to know the detail of one of those descriptions, say length, for a specific SKU, say 00001, and if the table was called products, then

"SELECT length FROM products WHERE SKU = 00001"

should do it.

If you want the entire row,

"SELECT * FROM products WHERE SKU = 00001"

[yellowlitegames]

How would I call a certain database that the information is in?

[kiwibrit]

Not sure I would be allowed to post a link here - but go to w3schools - and look at MySQL Select under the PHP Database section in the menu down the left hand side. I think that's what you are after. Maybe start at the beginning of the section and work through it.

[ski442]

Welcome to WM yellowlitegames,

I am still learning too, but I use an include.php file which has all the details like this

<?php
$dbcnx = mysql_connect ('SeverName', 'UserName', 'PassWord');
if (!$dbcnx) {
exit('<p>Unable to connect to database server at this time.</p>');

}

//select the database.
if (!mysql_select_db('DataBaseName')) {
exit('<p>Unable to connect to locate the products database at this time.</p>');

}

?>
Not tested so may need to be tweeked a little to suit your needs.
Hope this helps

[yellowlitegames]

So if I connect to the server then select what I want, for instance

<?php
$dbcnx = mysql_connect ('SeverName', 'UserName', 'PassWord');
if (!$dbcnx) {
exit('<p>Unable to connect to database server at this time.</p>');

}

//select the database.
if (!mysql_select_db('all')) {
exit('<p>Unable to connect to locate the products database at this time.</p>');

}

?>

"SELECT title FROM all WHERE product num = 0"

Then how would I display the title? I have tried using

echo "title";

but its not working. How could I display this info?

[ski442]

You will need to have a mysql_fetch_array after your select statment, there is loads of info on this one around.

I take that your database name is "all" and that you are not trying to connect to all of your DB's

[rocknbil]

Links to authority sites are allowed.

W3 Schools PHP/Mysql [w3schools.com]
PHP.net [us3.php.net] Bringing you in on the mysql_fetch_array() page, but there are samples everywhere there.

You connect to the database, then execute a query. The query is accessed via mysql_fetch_array. This returns **both** a numeric index array **and** an associative array. So to answer the first question,

select title from table where product=some_valid_id_number;

Since you selected one field both of these are identical:
$title = $row[0];
$title = $row['title'];
More to this story, see below. But first . . .

I've bolded the text above for a reason:

"SELECT title FROM all WHERE product num = 0"

There are two things wrong here. You shouldn't have, or be allowed to create, a field name with a space. This should have generated an error; if it didn't, okay, it means however you did it is managed by the software you used. But you will need to add backticks for this field:

"SELECT title FROM all WHERE `product num` = 0"

The second thing that's wrong here is you should never have a unique id of "zero." If you do, something is seriously wrong in how you set up the database. These should be auto_increment numbers (at the very least) and should always be unique.

So. Given all that:

// Store the query itself in a variable. 
$query = "SELECT title FROM all WHERE `product num` =5" 
// Execute the query, store the result in a variable. 
// If a SINGLE RESULT is expected, use IF. If you 
// expect multiple results, use WHILE. Since you 
// query one record, using IF 
$result=mysql_query($query); 
if ($row=mysql_fetch_array($result)) { 
 echo $row[0] . "<br>"; // And there you go, printing the title 
 // OR: echo $row['title']; 
 // Good habit, but not for many small queries, I'm told 
 mysql_free_result($result); 
} 
else { echo "Oops! no results found." } 

[yellowlitegames]

When I use this script it comes up 'Unexpected T_variable' for the $result=mysql_query($query); line. Does anyone know why that could be?

[mvaz]

This error usually is when you have missed a bracket or used an incorrect paranthesis (if that's how it is spelt - they are the square brackets).

[yellowlitegames]

16 <?php
17 $dbcnx = mysql_connect("localhost","user","pass");
18 if (!$dbcnx) {
19 exit('<p>Unable to connect to database server at this time.</p>');
20
21 }
22
23 //select the database.
24 if (!mysql_select_db('nnelsen_feed'))
25 {
26 exit('<p>Unable to connect to locate the products database at this
27 time.</p>');
28
29 }
30
31 $query = "SELECT 'title' FROM 'nnelsen_feed'
32 WHERE 'productid'=2fd1a957f485b1631c9d86e72e78459e";
33
34 // Store the query itself in a variable.
35 //$query=""
36 // Execute the query, store the result in a variable.
37 // If a SINGLE RESULT is expected, use IF. If you
38 // expect multiple results, use WHILE. Since you
39 // query one record, using IF
40 $result = mysql_query('$query');
41 if ($row = mysql_fetch_array('$result')) {
42
43 // And there you go, printing the title
44 echo $row['title'];
45 // Good habit, but not for many small queries, I'm told
46 //mysql_free_result($result);
47 }
48 ?>

This is my error.

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/nnelsen/public_html/alpha/Untitled-1.php on line 39

[rocknbil]

Sorry. I typed on the fly. The original sample,

$query = "SELECT title FROM all WHERE `product num` =5"

was simply mising the statement closing semicolon.

$query = "SELECT title FROM all WHERE `product num` =5" ;

I never expected anyone to run it, it was for example. :-)

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/nnelsen/public_html/alpha/Untitled-1.php on line 39

I find these messages from PHP extremely unhelpful. :-) Even "error in your mysql syntax" is better - and that's all it appears to be here. Look at this:

$query = "SELECT 'title' FROM 'nnelsen_feed'
WHERE 'productid'=2fd1a957f485b1631c9d86e72e78459e";

A little overzealous with the quotes, and they are misplaced. :-) You only need to quote textual values you are querying, not field or table names.. This gets confusing when you see backticks in select statements. By the same token, you do NOT have quotes on the value you are querying. At any rate, try this.

$query = "SELECT title FROM nnelsen_feed
WHERE productid = '2fd1a957f485b1631c9d86e72e78459e'";

[yellowlitegames]

Its now doing the smae error. But for this line now...

if ($row = mysql_fetch_array($result))

[TheMadScientist]

How about we switch up your error routine a bit?

mysql_connect("localhost","user","pass") or die ( 'No Connection: ' . mysql_error() . "\n");
mysql_select_db('nnelsen_feed') or die ( 'No DataBase Selected: ' . mysql_error() . "\n");

/* echo '<br>'.$query.'<br>'; If there is an error in the next line, uncomment the first portion of this line and run again to see the query.*/

mysql_query($query) or die ( 'No Query: ' . mysql_error() . "\n");

Generally the error you are receiving means there is something wrong with the query or there is no result found, which means you are looking for the wrong information, information that is not there, or information in the wrong col / table.

There are a limited number of options to keep you from getting a result, and they all come from the query, so it's usually good to echo it out if you're having issues, because you could be dropping a variable or using the wrong variable, or doing something I've been know to do and stare at trying to figure out once in a while... typing $theVariable when I mean to type $TheVariable. (Those are the best!)

Once you get it running, you can comment out the last portion of the lines and they are always there if you need them, or you can just remove them:

mysql_connect("localhost","user","pass"); /* or die ( 'No Connection: ' . mysql_error() . "\n"); */

[rocknbil]

Still crazy with the quotes, didn't see this on first look. :-)

40 $result = mysql_query('$query');
41 if ($row = mysql_fetch_array('$result')) {

Note that double quotes will allow variables to interpolate, single quotes will not. So in effect, your select statement is '$query', not "select * from . . . . "

You don't need these quoted at all. Fix it to this.

$result = mysql_query($query);
if ($row = mysql_fetch_array($result)) {

And adding the mysql_error() is highly recommended but be sure to remove it from your scripts before going live, it can reveal information about your database that can be used against you.

[yellowlitegames]

I have gotten it to run but it echos the whole select statement. Is there any way to fix that?

[rocknbil]

When you do this

echo $row['title'];

it's displaying the whole select statement?

Going to have to see the whole code, can't imagine why it would do that.

[yellowlitegames]

$dbcnx = mysql_connect("localhost","user","pass") //or die ( 'No Connection: ' . mysql_error() . "\n");

if (!$dbcnx) {
exit('<p>Unable to connect to database server at this time.</p>');

}

//select the database.
if (!mysql_select_db("nnelsen_feed"))

{
exit('<p>Unable to connect to locate the products database at this time.</p>');

}

$query = "SELECT title FROM nnelsen_feed.all WHERE productid = '2fd1a957f485b1631c9d86e72e78459e'";

//$query = "SELECT 'title' FROM 'nnelsen_feed' WHERE 'productid'=2fd1a957f485b1631c9d86e72e78459e";

// Store the query itself in a variable.
//$query=""
// Execute the query, store the result in a variable.
// If a SINGLE RESULT is expected, use IF. If you
// expect multiple results, use WHILE. Since you
// query one record, using IF
//echo '<br>'.$query.'<br>'; /*If there is an error in the next line, uncomment the first portion of this line and run again to see the query.*/
$result = mysql_query($query) //or die ( 'No Query: ' . mysql_error() . "\n");
if ($row = mysql_fetch_array($result)) {

// And there you go, printing the title
echo $row['title'];
// Good habit, but not for many small queries, I'm told
mysql_free_result($result);
}

[rocknbil]

Look at these two:

$dbcnx = mysql_connect("localhost","user","pass") //or die ( 'No Connection: ' . mysql_error() . "\n");

$result = mysql_query($query) //or die ( 'No Query: ' . mysql_error() . "\n");

See how you have commented "or die?" When you do that, you also comment out the ending statement semicolon. If you want those out, put the semicolon back in, at least temporarily:

$dbcnx = mysql_connect("localhost","user","pass"); //or die ( 'No Connection: ' . mysql_error() . "\n");

$result = mysql_query($query); //or die ( 'No Query: ' . mysql_error() . "\n");

If this is on two lines, like you have it here, this is also an error. Put it on one line, or comment them both, like

//$query = "SELECT 'title' FROM 'nnelsen_feed' WHERE // 'productid'=2fd1a957f485b1631c9d86e72e78459e";

And again, watch out for the quotes, do not quote field names like above.

Another little tidbit, put this at the top of your script, right after <?php :

ini_set('display_errors',1);
error_reporting(E_ALL);

It will display errors to the screen that normally just go into your server error log, and will help point to the problem areas.