[PHP-DB] **Happy New Year**
Hi PHP Coders, Wishing you all a very happy and prosperous 2004. Aynsoft www.aynsoft.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Problem with INSERT Query
Hi all:
Well, when i bring out the page with the drop down list it was able to display
all tutors' names from "tutor_name" column. Anyway here's a review of my code
(snip) again before i continue:
---
$sql = "INSERT INTO class (class_code, tutor_name, edu_level, timetable_day,
timetable_time)
VALUES
('$class_code','$tutor_name','$edu_level','$timetable_day','$timetable_time')";
" .$row
["tutor_name"]. " ";
}
$result = $db-> query($sql);
?>
---
so when i submit the form, i am suppose to echo the values i have entered into
the field and then INSERT the values into DB (Queries stated above). However i
was able to echo all other values eg. class_code, edu_level, etc...but
not "tutor_name"same thing happen when i do an INSERT, all other values
are inserted into DB but not $tutor_namewhy is this so???Really need some
help here...Anyway i have already specify a name to be reference :
and then I also did an echo of "tutor_name" being selected:
while($selected_tutor_name == $tutor_name)
echo $_POST["tutor_name"];
All help are greatly appreciated =)
Irin.
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
SV: [PHP-DB] Selecting between using letters
How about Select LastName from sometable where LastName >= 'A' and LastName <'F' Hth Henrik Hornemann -Oprindelig meddelelse- Fra: Doug Parker [mailto:[EMAIL PROTECTED] Sendt: 29. december 2003 23:18 Til: [EMAIL PROTECTED]; [EMAIL PROTECTED] Emne: [PHP-DB] Selecting between using letters How would I create a select statement in MySQL that would return a range of records from the LastName field where the value starts with a designated letter - for example, returning the range where the first letter of LastName is between A and E... Any help would be greatly appreciated. http://www.phreshdesign.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] **Happy New Year**
A day early =) ... but happy new year to you too Rene Fate would have it, that on Tue, 30 Dec 2003 12:17:29 +0530, <[EMAIL PROTECTED]> wrote: >Hi PHP Coders, > >Wishing you all a very happy and prosperous 2004. > >Aynsoft > >www.aynsoft.com Rene Brehmer aka Metalbunny http://metalbunny.net/ References, tools, and other useful stuff... -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: Problem with INSERT Query
<[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
>
>
> Hi all:
>
> Well, when i bring out the page with the drop down list it was able to
display
> all tutors' names from "tutor_name" column. Anyway here's a review of my
code
> (snip) again before i continue:
> --
-
>
> $sql = "INSERT INTO class (class_code, tutor_name, edu_level,
timetable_day,
> timetable_time)
> VALUES
>
('$class_code','$tutor_name','$edu_level','$timetable_day','$timetable_time'
)";
>
>
>
>
>
>
>
$sqltutor = mysql_query("SELECT DISTINCT tutor_name FROM tutor ");
while ($row = mysql_fetch_array($sqltutor))
{
print " " .$row
["tutor_name"]. " ";
> }
> $result = $db-> query($sql);
>
> ?>
>
>
>
> while($selected_tutor_name == $tutor_name)
> echo $_POST["tutor_name"];
>
How about
if ($tutor_name ){
echo $_POST["tutor_name"];
$sql = "INSERT INTO class (class_code, tutor_name, edu_level, timetable_day,
timetable_time)
VALUES
('$class_code','$tutor_name','$edu_level','$timetable_day','$timetable_time'
)";}
is it working?
Hadi
> ?>
>
>
>
> --
-
>
> so when i submit the form, i am suppose to echo the values i have entered
into
> the field and then INSERT the values into DB (Queries stated above).
However i
> was able to echo all other values eg. class_code, edu_level, etc...but
> not "tutor_name"same thing happen when i do an INSERT, all other
values
> are inserted into DB but not $tutor_namewhy is this so???Really need
some
> help here...Anyway i have already specify a name to be reference :
>
>
>
> and then I also did an echo of "tutor_name" being selected:
>
> while($selected_tutor_name == $tutor_name)
> echo $_POST["tutor_name"];
>
> All help are greatly appreciated =)
>
> Irin.
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Categories and Items query
Hello,
I am pretty new to PHP and MySQL, and am
struggling with this particular page I would like
to create.
I am building a page which lists categories of
house features (e.g. concrete, framing,
foundation, etc.) and then the particular features
that fall into those categories (e.g. the concrete
items are driveway, garage floor, sidewalk,
basement; the framing items include manufactured
floor joist, 1/2" OSB on roof, etc.) The tricky
part is that there are two lists of features I
need to produce: standard and deluxe. Some
features span both types, some are particular to
only one feature list.
I have created 3 tables to handle this:
- the feature_sets table has three items: both(1),
standard(2) only and deluxe(3) only
- the feature_categories table holds the 19
categories of features (concrete, framing, etc.)
- the features table holds all of the features (63
total.)
All tables have primary keys, and the features
table has 2 foreign keys corresponding to
featureset and the featurecategory.
What I would like to be able to do is to have the
category appear, and then list the corresponding
features under it, depending on whether it's the
deluxe of standard feature list. Here's my code:
require_once
('/usr/home/fahomes/mysql_connect.php');
$query = "SELECT category, feature_description
FROM features as f, feature_categories as fc WHERE
f.feature_category_id=fc.feature_category_id AND
f.featureset_id = '1' OR f.featureset_id = '3'
ORDER BY f.feature_category_id ASC";
$result = @mysql_query ($query);
if ($result) {
while ($row = mysql_fetch_array($result,
MYSQL_NUM)) {
echo "
$row[0]
$row[1]
\n";
}
mysql_free_result ($result);
} else {
echo 'The features could not be displayed due
to a system error. Please contact the mailto:[EMAIL PROTECTED]">webmaster.'
. mysql_error() . '';
}
mysql_close();
?>
What I am getting is initially it's creating a row
for each category/feature pair, so the categories
that have more than one feature listed appear in
more than one row. It works fine until record 34
(id 33) when it starts puking out that feature for
10 rows then the next feature for 1 row, then it
jumps back to the feature 33 for another 12 rows,
this time cycling through the categories. It's
quite bizarre:
http://roku.pair.com/fahomes/test/homes_dlx_features.php
So, I actually have two questions:
1) Does anyone know why my query is misbehaving so
badly? When I copy and paste the query into
MyPHPAdmin, it produces the same results, so I
know it's not the php. On thing I do notice is if
I change the query slightly (so that it looks for
records "WHERE
f.feature_category_id=fc.feature_category_id AND
f.featureset_id = '1' OR '3'" instead of
"f.featureset_id = '1' OR f.featureset_id = '3'"
it produces equally messed up, but different results.
2) Is there a way that I can display the data the
way I want to display it (list the category once,
then corresponding features underneath, before
going to the next category)? I would imagine it's
a WHILE loop that's inside another WHILE loop -- I
am just not sure how to go about it, and haven't
been able to track down anything in my books.
Thanks for any advice!
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: php-db Digest 30 Dec 2003 12:07:25 -0000 Issue 2191
Try this: select LastName from MyTable where left(LastName,1) >= 'a' and left(LastName,1) < 'f'; How about Select LastName from sometable where LastName >= 'A' and LastName <'F' Hth Henrik Hornemann -Oprindelig meddelelse- Fra: Doug Parker [mailto:[EMAIL PROTECTED] Sendt: 29. december 2003 23:18 Til: [EMAIL PROTECTED]; [EMAIL PROTECTED] Emne: [PHP-DB] Selecting between using letters How would I create a select statement in MySQL that would return a range of records from the LastName field where the value starts with a designated letter - for example, returning the range where the first letter of LastName is between A and E... Any help would be greatly appreciated. http://www.phreshdesign.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Checking for duplicate records before update?
Problem. I have a database table that looks like this:
eg.
+--+--+---+---+-+
| id | hostname | mac | ip| vlan|
+--+--+---+---+-+
| 1014 | MTPC-01 | 00:02:B3:A2:9D:ED | 155.97.15.11 | Vlan-29 |
| 1015 | MTPC-02 | 00:02:B3:A2:B6:F4 | 155.97.15.12 | Vlan-29 |
This table will hold a very large number of entries, all unique. I have
created a simple HTML form which updates the entries but before it does
it checks to see if records already exist that contain either the same
IP value, the same MAC value or the same Hostname value. If any of
these items exist it currently discards the posted informaiton and
prompts the user to re-enter because I cannot have duplicate entries of
either the hostname, mac, ip fields.
Here is my function:
eg.
$x = mysql_query("SELECT * FROM $table WHERE hostname =
'$_POST[hostname]' OR ip = '$_POST[ip]' OR mac = '$_POST[mac]' NOT id =
'$_SESSION[id01]'")or die(mysql_error());
$num = mysql_num_rows($x);
if($num == "0") {
unset($_SESSION['search']);
require 'dbase.inc.php';
$table = "hosts";
$sql = @mysql_query("UPDATE $table SET hostname =
\"$_POST[hostname]\", mac = \"$_POST[mac]\", ip = \"$_POST[ip]\", vlan =
\"$_POST[vlan]\" WHERE id = \"$_SESSION[id01]\"")or die(mysql_error());
echo "Form worked!";
} elseif ($num != 0) {
unset($_SESSION['search']);
echo "Form didn't work because 1 of the 3 fields were present in
another record!!! Please try again.";
} else {
echo "RTM again please!"; }
I think what I really need to know is if there is a quick way to sort
through the results of the current database records and do comparisons
against the form. If any one has done something like this before could
you please show me an example or point me to a good resource. Thanks in
advance.
Jas
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Checking for duplicate records before update?
Hi
This is an alternate approach, and may be more elegant and efficient
Define hostname, mac, and ip fields as UNIQUE.
then INSERT IGNORE into the table. Check to see if a row was inserted using
mysql_affected_rows()
http://www.mysql.com/doc/en/mysql_affected_rows.html
if the row was inserted then all is well, otherwise ask to resubmit
I think this should still work with auto ids, but check.
HTH
Peter
-Original Message-
From: Jas [mailto:[EMAIL PROTECTED]
Sent: 30 December 2003 22:32
To: [EMAIL PROTECTED]
Subject: [PHP-DB] Checking for duplicate records before update?
Problem. I have a database table that looks like this:
eg.
+--+--+---+---+-+
| id | hostname | mac | ip| vlan|
+--+--+---+---+-+
| 1014 | MTPC-01 | 00:02:B3:A2:9D:ED | 155.97.15.11 | Vlan-29 |
| 1015 | MTPC-02 | 00:02:B3:A2:B6:F4 | 155.97.15.12 | Vlan-29 |
This table will hold a very large number of entries, all unique. I have
created a simple HTML form which updates the entries but before it does
it checks to see if records already exist that contain either the same
IP value, the same MAC value or the same Hostname value. If any of
these items exist it currently discards the posted informaiton and
prompts the user to re-enter because I cannot have duplicate entries of
either the hostname, mac, ip fields.
Here is my function:
eg.
$x = mysql_query("SELECT * FROM $table WHERE hostname =
'$_POST[hostname]' OR ip = '$_POST[ip]' OR mac = '$_POST[mac]' NOT id =
'$_SESSION[id01]'")or die(mysql_error());
$num = mysql_num_rows($x);
if($num == "0") {
unset($_SESSION['search']);
require 'dbase.inc.php';
$table = "hosts";
$sql = @mysql_query("UPDATE $table SET hostname =
\"$_POST[hostname]\", mac = \"$_POST[mac]\", ip = \"$_POST[ip]\", vlan =
\"$_POST[vlan]\" WHERE id = \"$_SESSION[id01]\"")or die(mysql_error());
echo "Form worked!";
} elseif ($num != 0) {
unset($_SESSION['search']);
echo "Form didn't work because 1 of the 3 fields were present in
another record!!! Please try again.";
} else {
echo "RTM again please!"; }
I think what I really need to know is if there is a quick way to sort
through the results of the current database records and do comparisons
against the form. If any one has done something like this before could
you please show me an example or point me to a good resource. Thanks in
advance.
Jas
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: Categories and Items query
Well, I figured out how to do my nested loop, so now I think it's a
fairly small problem that I have now: how to only select features that
should show on the Deluxe Home Features page, or the Standard Home
Features page.
I have a table named feature_sets with three values (plus keys): both,
standard only or deluxe only. I designed it this way because for some
features, such as the wood trim, there is an upgraded version (e.g. oak
vs. pine), some features are common to both homes, and a couple are
exclusive to the deluxe home.
I have my nested loop working so it lists the category then lists the
features in that category in a bulleted list, but it's currently pulling
out ALL of the features, not just the "all homes" and "deluxe" (which is
the page I am working on. When I try to add an additional AND statement
to limit the features to only those with 1 or 3 as their featureset_id,
I end up with a list that has ALL the features showing up under each of
the categories.
Is there a way that I can limit my query to just two of the three
featureset IDs?
Any help would be much appreciated!
My code:
$query_categories = "SELECT * FROM feature_categories";
$categories = mysql_query($query_categories) or die(mysql_error());
$row_categories = mysql_fetch_assoc($categories);
$totalRows_categories = mysql_num_rows($categories);
?>
?>
$query_features = "SELECT feature_description FROM features WHERE
feature_category_id = ".$row_categories['feature_category_id']."";
$features = mysql_query($query_features) or die(mysql_error());
$row_features = mysql_fetch_assoc($features);
$totalRows_features = mysql_num_rows($features);
do { ?>
} WHILE ($row_categories = mysql_fetch_assoc($categories));
mysql_free_result($categories);
mysql_free_result($features);
mysql_close();
?>
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: Categories and Items query
Having a Standard only, Deluxe only and Both will lead to problems in the
future. (i.e. what if you add in a Basic feature set?) What I would do is
1. Get rid of the "Both" row in feature sets.
2. Have another table called "feature_set_features" or something better. The
table has two columns, The "Feature id" and the "Feature set ID". If a
feature is in one feature set, there in one row in the table. if a Feature
is in Both feature sets, then there are two rows in this table, one for
each feature set.
3. To query this, join features and feature_set_features on feature_id and
use where feature_set_id = 'standard' or whatever if is.
Hopefully I haven't confused you too much.
Lang
Ali Van Doren wrote:
> Hello,
> I am pretty new to PHP and MySQL, and am
> struggling with this particular page I would like
> to create.
> I am building a page which lists categories of
> house features (e.g. concrete, framing,
> foundation, etc.) and then the particular features
> that fall into those categories (e.g. the concrete
> items are driveway, garage floor, sidewalk,
> basement; the framing items include manufactured
> floor joist, 1/2" OSB on roof, etc.) The tricky
> part is that there are two lists of features I
> need to produce: standard and deluxe. Some
> features span both types, some are particular to
> only one feature list.
> I have created 3 tables to handle this:
> - the feature_sets table has three items: both(1),
> standard(2) only and deluxe(3) only
> - the feature_categories table holds the 19
> categories of features (concrete, framing, etc.)
> - the features table holds all of the features (63
> total.)
> All tables have primary keys, and the features
> table has 2 foreign keys corresponding to
> featureset and the featurecategory.
>
> What I would like to be able to do is to have the
> category appear, and then list the corresponding
> features under it, depending on whether it's the
> deluxe of standard feature list. Here's my code:
>
> require_once
> ('/usr/home/fahomes/mysql_connect.php');
> $query = "SELECT category, feature_description
> FROM features as f, feature_categories as fc WHERE
> f.feature_category_id=fc.feature_category_id AND
> f.featureset_id = '1' OR f.featureset_id = '3'
> ORDER BY f.feature_category_id ASC";
> $result = @mysql_query ($query);
> if ($result) {
> while ($row = mysql_fetch_array($result,
> MYSQL_NUM)) {
>echo "
>$row[0]
> align=\"left\">$row[1]
>\n";
> }
> mysql_free_result ($result);
> } else {
> echo 'The features could not be displayed due
> to a system error. Please contact the href=\"mailto:[EMAIL PROTECTED]">webmaster.'
> . mysql_error() . '';
> }
> mysql_close();
> ?>
>
>
> What I am getting is initially it's creating a row
> for each category/feature pair, so the categories
> that have more than one feature listed appear in
> more than one row. It works fine until record 34
> (id 33) when it starts puking out that feature for
> 10 rows then the next feature for 1 row, then it
> jumps back to the feature 33 for another 12 rows,
> this time cycling through the categories. It's
> quite bizarre:
> http://roku.pair.com/fahomes/test/homes_dlx_features.php
>
> So, I actually have two questions:
> 1) Does anyone know why my query is misbehaving so
> badly? When I copy and paste the query into
> MyPHPAdmin, it produces the same results, so I
> know it's not the php. On thing I do notice is if
> I change the query slightly (so that it looks for
> records "WHERE
> f.feature_category_id=fc.feature_category_id AND
> f.featureset_id = '1' OR '3'" instead of
> "f.featureset_id = '1' OR f.featureset_id = '3'"
> it produces equally messed up, but different results.
>
> 2) Is there a way that I can display the data the
> way I want to display it (list the category once,
> then corresponding features underneath, before
> going to the next category)? I would imagine it's
> a WHILE loop that's inside another WHILE loop -- I
> am just not sure how to go about it, and haven't
> been able to track down anything in my books.
>
> Thanks for any advice!
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: Categories and Items query
Thanks, Lang! I think I understand your description, and will try it
tonight. Thanks so much for responding!
:)
Lang Sharpe wrote:
Having a Standard only, Deluxe only and Both will lead to problems in the
future. (i.e. what if you add in a Basic feature set?) What I would do is
1. Get rid of the "Both" row in feature sets.
2. Have another table called "feature_set_features" or something better. The
table has two columns, The "Feature id" and the "Feature set ID". If a
feature is in one feature set, there in one row in the table. if a Feature
is in Both feature sets, then there are two rows in this table, one for
each feature set.
3. To query this, join features and feature_set_features on feature_id and
use where feature_set_id = 'standard' or whatever if is.
Hopefully I haven't confused you too much.
Lang
Ali Van Doren wrote:
Hello,
I am pretty new to PHP and MySQL, and am
struggling with this particular page I would like
to create.
I am building a page which lists categories of
house features (e.g. concrete, framing,
foundation, etc.) and then the particular features
that fall into those categories (e.g. the concrete
items are driveway, garage floor, sidewalk,
basement; the framing items include manufactured
floor joist, 1/2" OSB on roof, etc.) The tricky
part is that there are two lists of features I
need to produce: standard and deluxe. Some
features span both types, some are particular to
only one feature list.
I have created 3 tables to handle this:
- the feature_sets table has three items: both(1),
standard(2) only and deluxe(3) only
- the feature_categories table holds the 19
categories of features (concrete, framing, etc.)
- the features table holds all of the features (63
total.)
All tables have primary keys, and the features
table has 2 foreign keys corresponding to
featureset and the featurecategory.
What I would like to be able to do is to have the
category appear, and then list the corresponding
features under it, depending on whether it's the
deluxe of standard feature list. Here's my code:
$row[0]
$row[1]
\n";
}
mysql_free_result ($result);
} else {
echo 'The features could not be displayed due
to a system error. Please contact the mailto:[EMAIL PROTECTED]">webmaster.'
. mysql_error() . '';
}
mysql_close();
?>
What I am getting is initially it's creating a row
for each category/feature pair, so the categories
that have more than one feature listed appear in
more than one row. It works fine until record 34
(id 33) when it starts puking out that feature for
10 rows then the next feature for 1 row, then it
jumps back to the feature 33 for another 12 rows,
this time cycling through the categories. It's
quite bizarre:
http://roku.pair.com/fahomes/test/homes_dlx_features.php
So, I actually have two questions:
1) Does anyone know why my query is misbehaving so
badly? When I copy and paste the query into
MyPHPAdmin, it produces the same results, so I
know it's not the php. On thing I do notice is if
I change the query slightly (so that it looks for
records "WHERE
f.feature_category_id=fc.feature_category_id AND
f.featureset_id = '1' OR '3'" instead of
"f.featureset_id = '1' OR f.featureset_id = '3'"
it produces equally messed up, but different results.
2) Is there a way that I can display the data the
way I want to display it (list the category once,
then corresponding features underneath, before
going to the next category)? I would imagine it's
a WHILE loop that's inside another WHILE loop -- I
am just not sure how to go about it, and haven't
been able to track down anything in my books.
Thanks for any advice!
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: Selecting between using letters
Im on my way to bed so short do it yourself answer, but you should look up the regex part of mySQL. I think you can match the beginning and end of a coloumns entry. And using the power of the regex function you could make a WHERE statement for somethink like [a-e] if it was a-e you were looking for. Remember ofcourse that you wuld need to use the regex syntax. The page on mySQL covering the topic has a lot of examples. -- Kim Steinhaug --- There are 10 types of people when it comes to binary numbers: those who understand them, and those who don't. --- "Doug Parker" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > How would I create a select statement in MySQL that would return a range of > records from the LastName field where the value starts with a designated > letter - for example, returning the range where the first letter of LastName > is between A and E... > > Any help would be greatly appreciated. > > > > > http://www.phreshdesign.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
