Trotty

Okey kokey, I'm trying to get to grips with the above at the moment. I've installed all the relevant programs locally for testing.

I've created a lil' db called test and a table called stuff in mySQL.

In my PHP code I have the following:
$connect = @mysql_connect
$db_select = @mysql_select_db($database) or die("db error");

$SQL = "SELECT * FROM $table";
And so on...

The $database and $table variables are assigned higher up the code.

However, my problem is when I vew the page I get the following error:
Parse error: parse error, unexpected T_VARIABLE in my_local_path\test.php on line 39


Line 39 is my $db_select = @mysql... line - anyone see anything wrong?
All required services are running, the db and table definately exist and PHP is quite happy. Getting a bit confused now

Ian

[Edited by Trotty - 7/3/2002 9:49:43 AM]

Trotty

What a tool!
No-one noticed my mistake though...
I was selecting:
$query = "SELECT * FROM test";

When it should have been:
$query = "SELECT * FROM stuff";

Test is the db name, stuff is the table name - and ta-da! It now works

All I need to do now is work out how to displayed a db item depending on what is selected from a dropdown combo box...

D'oh!

[Edited by Trotty - 7/3/2002 11:11:39 AM]

legacyPete

Right... here we go

You don't need queries on submit buttons (if I'm getting what you mean). It's quite straightforward really, and if you want to be really clever you can build up queries using arrays and variable variables...

EXAMPLE:

This example uses two tables - towns and bookings

towns:
townID
townName

bookings:
bookingID
name
townID

To put info into a combo box put this in a form:

<select name="townID" class="input">
<?
$townquery = mysql_query("SELECT townID, townName from towns order by townName ASC");
while($towns = mysql_fetch_array($townquery)) {
echo"<option value=\"".$towns["townID"]."\">".$towns["townName"]."</option>";
}
?>
</select>

The form is submitted to another page (or the same one if you want) and then queries the database using the selected townID from the form:

if($id) {
// perform query and display stuff
$query = mysql_query("select bookings.*, towns.* from bookings, towns where bookings.bookingID = $townID AND bookings.townID = towns.townID");
$data = mysql_fetch_array($query);
// ...output stuff
} else {
// ...tell 'em to bugger off
}
?>

That's a basic example to give you an idea of what to do, it can be made a lot better. If you want a better example or need more help I could email you some pages.
Pete

[Edited by legacyPete - 7/3/2002 3:35:12 PM]

legacyPete

That's an easy one

$query = "SELECT * FROM data WHERE part like \"$series\"";

Best to escape the value using \" as the input is free text.

To make it case-insensitive you can put \"%$series%\" in the query instead (I think).

Any form objects are turned into variables when they are submitted, hence $series; just put the dollar in front!

TIP:

instead of
or die ("bugger");

try putting
or die (mysql_error());

The error message isn't as amusing, but it may help you

[Edited by legacyPete - 7/4/2002 11:33:39 AM]

Trotty

Pete your a star!
That makes sense once I've seen it...
So I suppose I could still do the combo box, but manually populate the options, and pass the selection as the query variable? Like:

<form action="result.php" method="post">
<select name="select">
<option>EX</option>
<option>VEX</option>
<input type="submit" value="Search">
</select>
</form>

And then use "\$select%\""?

Ian

[Edited to say]
~ Woohoo, it does work ~

Thanks for that Pete, really appreciate it.

[Edited by Trotty - 7/4/2002 11:58:12 AM]

legacyPete

Yes, it really is that easy!

However, instead of buggering about doing the combo yourself, get the info from the database:

<select name="select" class="input">
<?
$seriesquery = mysql_query("SELECT distinct series from data order by series group by series");
while($seriesdata = mysql_fetch_array($seriesquery)) {
echo"<option value=\"".$seriesdata["series"]."\">".$seriesdata["seriesdata"]."</option>";
}
?>
</select>

Yes, then you would use $select, or whatever you called the form object.

[Edited to say]
Glad I could help!

[Edited by legacyPete - 7/4/2002 12:07:30 PM]

Trotty

Bugger! Right, fixed the above problem by changing:
$connect = @mysql_connect
$db_select = @mysql_select_db($database) or die("db error");

To:

mysql_connect("localhost", "usernamehere", "passwordhere");
mysql_select_db("dbname") or die ("db error");


However, now I've come across another oddity? My next piece of code just select all my data from the table and spits it out - well, it should... The code is:

$SQL = "SELECT * FROM test";
$result = mysql_query($SQL) or die("could not complete your query");
while($row = @mysql_fetch_array($result)) {
$id = $row["id"];
$name = $row["name"];
$description = $row["description"];
echo "$name - $description <P>";
}

I now get "could not complete your query"
Does $result need to be defined as a variable somewhere? I got the impression that it didn't need to be?

Anyone...

Ian

snowcrash

uhm, answered it yourself

Trotty

Well, I like to share...

snowcrash

php and mysql are lovely i have to say - pisses all over asp and M$ based crap

Trotty

Does seem pretty good, well, from my first impressions anyway.

Just trying to work out how someone can select something from a combo box (or two) and then have mySQL lookup the relevant data and display it...

I gather I need to assign a query to the submit button of my form but would I need a shedload of if...else statements to account for all the options?

Slowly getting lost!

dsmith

or a switch statement.....

Trotty

Thanks for the response gents.
Pete - that's kind of what I'm tring to do. Basically I'd only have one (pretty big) table and when the user selects two possible attributes the query would look up and display all available options at the bottom of the same page (if poss).

The reason is, the company I work for sells precision bearings. They are classified by various attributes but the main two are series and bore size. We want the page to list all series and bores in two lists and when two have been selected the bottom part of the page will list all available options for those bearings (preloads, pairings, etc).

Badly explained but hopefully you know what I mean...

Thanks,

Ian

dsmith

You see that sort of proper response is why people (i.e. me ) who are only a couple of pages further ahead in "php for dummies" should keep their mouths shut

Trotty

Heh heh heh, well, all input is appreciated

legacyPete

Ok then, have two combo boxes called series and bore like in my above post. Change the queries to:

series:
select distinct series from catalogue order by series

bore
select distinct bore from catalogue order by bore

Then when the search is submitted use a query like:
select * from catalogue where series = \"$series\" and bore = \"$bore\" order by series, bore

You can't get the page to be updated without it refreshing, so you would just have the page submit to itself and display any search results underneath the search form. Or you could use (whisper it) a frameset with the search in one frame and the result page in another??

I would advise trying to split the data into different tables as it makes searching more efficient and also reduces duplicate data - e.g having a series table with a unique seriesID for different series names / codes. Then in your catalogue table you would reference the seriesID. This is a lot easier to search and also means that if the series name / code changes you only have to change 1 record. However, without knowing more about the data it's hard to say what the best method would be.

Trotty

Ok, I see. Well, a typical part no. for us would be:
VEX257CE1

VEX is the series, 25 the bore, 7 the precision, CE1 the contact angle. However, a single bearing (listed above) can be put into different sets so you could have:

VEX257CE1
VEX257CE1UL
VEX257CE1DUL
VEX257CE1TDL

And so on...

I was planning on having one table with all parts in and then pulling out all available ones depending on series/bore selection. Couldn't think of an easier way to split the data up? (DB's never were a strong point!)

Any help?

legacyPete

I'm a bit confused by the set thing, are they treated as individual items?

Having one table will work, it's just that it's not so good to have lots of repeating data.

If you try the combo boxes and search in my previous post, it should work - just change the queries accordingly. It's hard to explain it properly so if there's anything I haven't explained very well, just say.

Trotty

Thanks for that Pete, I'll have a good look at it tomorrow and see how it goes.

Ian

Trotty

Okey dokey, clearly I'm crap at all this malarkey Shoudn't have started dabbling...

Just to get going I've change the idea a bit. I've now got two pages, tech.php and result.php. I've stuck a text entry field in tech.php and the result from that, when submitted gets passed over as series.
I'm now trying to get the query on result.php to use series as a query variable? Here's my code:

tech.php
<form action="result.php" method="post">
<font size="2">Bearing Series:</font>
<input type="text" name="series">
<input type="submit" value="Search">
</form>

result.php
<?php
$query = "SELECT * FROM data WHERE part like ???";
$result = @mysql_query($query) or die("bugger!");

while($row = @mysql_fetch_array($result)) {
$id = $row["id"];
$part = $row["part"];
$description = $row["description"];
echo "$part - $description<P>";
}
?>
All I want to do is change the query so it selects 'part like ' series typed in first page. I thought this would be easier than the combo box idea for now...

When the form submits the data does it 'carry it over' as series or Search?

Sorry for the length of post! Not too hot at this php stuff...

Cheers for any help,

Ian

[Edited by Trotty - 7/4/2002 10:28:12 AM]

[Edited by Trotty - 7/4/2002 10:28:55 AM]

Trotty

Just tried the above - works like a good 'un!
Now to setup a Series and size multiple select - should be the same as the earlier example wouldn't it?

legacyPete

Yep, should be v. similar, just change names and the queries a bit