Select Query - Count Does Not Return Correct Result
I have "enrollment" table with the following structure:
CREATE TABLE `enrollment` (
`enrollmentID` bigint(20) NOT NULL auto_increment,
`coursename` varchar(200) NOT NULL default '',
`studentID` int(4) NOT NULL default Ɔ',
`semestername` varchar(100) NOT NULL default '',
PRIMARY KEY (`enrollmentID`)
)
This table is populated with coursenames that students have enrolled in a particular semester. Given a semestername and coursename, my Select query is trying to retrieve all the coursenames and count of the number of students enrolled in each of the courses for the given semester (except for the given course). Code:
View Complete Forum Thread with Replies
Sponsored Links:
Related Messages:
Same Mysql Query Doesn't Always Return A Result
We have no access to a mysql NG on my provider's server, so we ask here: We have a long query (long in text) with a UNION between 2 select. We have been informed that some times the query doesn't return any result. We have tried on our server and we always get a result. BUT, trying on the hosting server, many times the query doesn't return any result and doesn't get any error. Any idea ? does Union have any problem ? how to check if the query failed ? My hoster said that sometimes the table can be locked (if the server is overloaded) and then mysql doesn't return any result, but this seems to me an aberration. the query takes about 0.0050 sec to execute when it doesn't return any result, and 0.030 when I get results
View Replies !
View Related
Turn Select Query Result Into Hyperlink To Other Query
I have a query which gives results of selecting coursenames from a table called trainingtopics but this does so in a continuous bulk of text without any breaks between each record. my question how do I create line breaks between each record and also how can I force each query result to become a hyperlink which when clicked runs another query that gives details of that course. <?php $user = "root"; $host = "localhost"; $password = ""; $connection = mysql_connect($host, $root, $password) or die ("Couldn't connect to server."); $database = "courses"; $db = mysql_select_db($database) or die ("Couldn't select database."); $sql = "SELECT coursename FROM trainingtopics"; echo $sql."=sql<br>"; $result = mysql_query($sql) or die(mysql_error()); echo $result."=result<br>"; while($row=mysql_fetch_array($result)) { // NOTE this one ABOVE the echo echo "result found!"; echo $row[0]; } ?>
View Replies !
View Related
Multiple SELECT COUNT()'s In One Query?
I'm creating a script that, fairly often throughout, I need to use a SELECT COUNT() query on two seperate tables - this seems to be slowing the script down quite a bit, and if the script remains this slow, it won't be of much use to me...which would be very bad! Is there anyway to combine these two to grab the COUNT() value from both in one query?
View Replies !
View Related
Manipulating Result On A SELECT Query
I have the following table: +-------------------------+ | id | tid | type | value | +-------------------------+ | 1 | 23 | 1 | 8 | | 2 | 21 | 1 | 6 | | 3 | 21 | 2 | 4 | | 4 | 21 | 2 | 9 | | 5 | 22 | 2 | 9 | | 6 | 22 | 1 | 10 | | 7 | 23 | 1 | 5 | | 8 | 22 | 1 | 4 | | 9 | 21 | 2 | 7 | | 10 | 22 | 2 | 7 | +-------------------------+ and I want to get the result like this: Code:
View Replies !
View Related
Select Count Query, 2 Tables With Identical Structures
I have 2 Tables that have the exact same structures, One is for Inventory, the other is for Relief Inventory (ie. when an item is sold it is moved to relief). What i want to be able to do is to run a query that will count the number of items tested by one person and display that sorted and grouped by the date cleared. I can get this to work when working with just the Inventory table, but the problem is that as items are moved out to relief the counts will change. $query = "SELECT COUNT( * ) AS `Count` , `Date Cleared` , `Tested By` FROM `Inventory` WHERE `Tested By` = ".'"'.$tester.'"'." GROUP BY `Date Cleared` ORDER BY `Date Cleared` DESC "; My question is, how do i run a similar query that will search both tables and combine the results. I have tried adding the other database to the FROM statement, and have been tring to figure out if i can use a join statement but that hasn't yielded any favorable results.
View Replies !
View Related
Displaying The Correct Result
I am getting a value from the table of the field no_of_semester. suppose the no_of_semester for the programe BCS are 8. Now i have to put the no_of_semester value in a listbox but the no_of_semester will start from 1,2,3.....8, which will be defenetly done with the help of a loop. Code:
View Replies !
View Related
Script Not Returning Correct Numerical Result
I am working on a script to calculate returns on bets. This involves entering odds, stake etc. The problem I am having is with adding a new element into the calculation. In betting their is something called a Rule 4. This occurs in some races and diminsihes the retrun but there is no need to go into details. Basically, here is how it works: There is a race with odds of 2/1 and a rule 4 of 20 cents is in place. With the betting rules this means that the odds must be multiplied by 1 - rule 4. In this case the rule 4 is .20 so 1 - .20 = .80 You now have to mulpiply the odds by .80 We only multiply the first number os in the case that is 2 (for a 5/1 bet it would be 5 etc) .80 * 2 = 1.6 is the correct answer. This calculates ok in part of my script but when it gets to the second part on the same page it is returning 1.28 instead. PHP Code:
View Replies !
View Related
Count() And The Result
I have a database table with a number of topics and a separate table with comments related to each topic by id number. I'm trying to setup a thing to display the number of comments for each topic, but, when I get to topics that have no comments at all, I'm not getting zero, I'm getting. not sure. What am I getting and how do I test for it so I can set it to zero? In other words, what is the result set of a count() that returns nothing because there is nothing to count?
View Replies !
View Related
Return Result Set In SPs
how can we have a select satement in an SP?(i want to return a result set from the SP). i want to migrate from SQL Server to MySQL and i don't know what to do with these SPs. it can be done in MySQL client, but it gives the error:"procedure foo can't return a result set in the given context MySQL" in the application. can i set a cursor out put parameter and return it?(just like Oracle) or it has another solution? and another question: is MyODBC 3.51 tested with MySQL 5.x? can we call an SP from Delphi via MyODBC? (i tried to do so (using ADO an a data provider for OLEDB) but i think somewhere in passing the call statement it had put {} around "call sp()" statement so that mysql returned an error about the syntax.) .
View Replies !
View Related
ORDER By COUNT(*) Result
Is there a way to order by the result of a COUNT(*) query? $result = mysql_query("SELECT ref_page, COUNT(*) FROM unique_views GROUP BY ref_page"); $myrow = mysql_fetch_array($result); In the above query I want to order the results by the result of the COUNT(*) e.g. PHP Code:
View Replies !
View Related
How Do I Return Result Of Mysql Row Deletion
The data entered by a user on form 1 on page1.php is posted to delete.php to remove that row from a table. After the SQL operation the user is returned to page1.php. How can I determine the success or failure of the SQL operation so I can display an appropriate response message to the user?
View Replies !
View Related
Mysql+php Newbie: 2 Tables As 1 Result To Count... How To?
I need to check for username in 2 tables (user_basic and user_temp) before I can alow incoming user to register new username, well, here is what I had before I'w created temporary table: $uname_check = mysql_query("SELECT uname FROM user_basic Where (uname='$uname')"); if (mysql_num_rows($uname_check) > 0) { ... but now, that mysql_num_rows isn't "working"... now I have this lines: $uname_check = mysql_query("SELECT uname.user_basic,uname.user_temp FROM user_basic,user_temp Where (uname='$uname')"); if (mysql_num_rows($uname_check) > 0) { ... and I'm geting this worning and my script fails in checking: "mysql_num_rows(): supplied argument is not a valid MySQL result resource in..."
View Replies !
View Related
Sql Query Not Returning Correct Results
i'm using an entry in an sql database as a semaphore and it, unfortunately, doesn't appear to be working. the script that spawns the child processes has a for loop that performs the same basic sql query over and over again until the value has changed (there's a one second delay between each query). unfortunately, the resultant row is always the same, even when the other process *has* changed it. as an example, if the orig. value was of the entry was 0, and one second into the execution of the second process, the entry was changed to 1, my program would, even 10 seconds later, fetch 0. in other words, it'd never be aware of the change. any idea as to why this is and what i can do to fix it?
View Replies !
View Related
Mysql_free_result Return Error Saying Not Valid Mysql Result Resource
im trying to do a query which takes rows of large amounts of text data and displays them one after another. the size of the fields is causing the page to be massive in size. so to try and reduce this im trying to use mysql_free_result to clear the contents from memory. however its returning an error saying not valid mysql data when im certain that it is. is this because im using a while loop like this...? ($row = mysql_fetch_result($result)) { mysql_free_result($result); }
View Replies !
View Related
Search Multiple Tables - Get The SQL Query Correct
I am trying to search mutiple tables in my PHP code. I believe I only need to get the SQL query correct. Basically I have maybe 5 table where I need t oselect all the data from them but I only want to search one of the fields in each of those tables. I made a post on the SQL boards but I think it will involve more PHP now because I believe I need to use a join I have something like $search=$_POST["search"]; Code:
View Replies !
View Related
Speeding Up Query/code (query Within Result Set)
I'm looking for ideas on how to speed up this script. Basically it finds all the zip codes in a zipcode table, then looks for all the records in another table with those zip codes. Right now it finds all the zips then within that WHILE, it looks for a record in another table with that zip: PHP Code:
View Replies !
View Related
Multiple Select From Listbox And Execute A Sql Query Select Statement
Supposing i have a html form with 2 listbox and i name it status and resolution. So it goes <select name='status[]'>... <select name='resolution[]'> Then i have a another php page. $status = $_POST['status']; $resolution = $_POST['resolution']; Now my question is, how do i do the sql query statement? mysql_query("SELECT * from bugs where status=$status and resolution=$resolution"); The problem is, i select 3 options form the status listbox eg. new, closed, duplicate. And from the resolution, i chose fixed, invalid.
View Replies !
View Related
Help With Select Count In PHP
I'm trying to do a script that tells me how many orders I got in on a particular day. The script loads, but nothing shows up. Any help would be greatly appreciated! Here it is: <? //create connection $connection = mysql_connect (localhost, xxxxxx, xxxx) or die ("Couldn't connect to server."); // select database // substitute your own database name $db = mysql_select_db (MyDB, $connection) or die ("Couldn't select database."); $date = "02/05/02"; // create SQL statement $sql = "SELECT COUNT(*) FROM CUSTOMER as mycount WHERE CREATED='$date' AND STATUS=Ɔ'"; // execute SQL query and get result $sql_result = mysql_query($sql,$connection) or die ("Couldn't execute query."); //start results formatting echo "<br><br><div align=center>"; echo "<table cellpadding=5 cellspacing=0 width=ྛ%'>"; echo "<tr bgcolor='#336699'> <td width=100% valign=top><font face='verdana,arial,helvetica' size='-2' color='#ffffff'><b>Orders Today:</b></font></td> </tr>"; // format results by row while ($row = mysql_fetch_array($sql_result)) { $mycount = $row["mycount"]; echo "<tr> <td width=100% valign=top><font face='verdana,arial,helvetica' size='-2' color='#000000'>$mycount</font></td> </tr>"; } echo "</table>"; echo "</div>"; /free resources and close connection mysql_free_result($sql_result); mysql_close($connection); ?>
View Replies !
View Related
SELECT COUNT(*)
I have the following query SELECT COUNT(*) as Num FROM files GROUP BY categoryid The problem is that if I run this query in phpmyadmin, I get a total of 4, which is right. But if I run this query within a php page, I only get 2 as a result. Can anyone tell me why I get a different result?
View Replies !
View Related
Select And Count
This works and I can echo $grid and all three user_id shows up, but I also want to echo how many it found and in this case it is three. HTML Code: $query = "SELECT * FROM members where girl = Ɔ' and user_id = '$user2'"; $result = mysql_query($query) or die( "ERROR: " . mysql_error() . " "); while ($myrow = mysql_fetch_array($result)) { $gird=$myrow["user_id"]; echo"$gird<BR>"; } So result should be like this : 1 2 3 ( 3 ) user_id found
View Replies !
View Related
Select Count (*)
This gives me back the results in some sort of an array, but I don't know why. Code: $query = $db->sql_query("SELECT COUNT(*) FROM " . $prefix . "_ladderteams WHERE army = 'Axis'"); $result = $db->sql_fetchrow($query) or die("ERROR: $query.".mysql_error()); $axiscount = $result; I have the same sort of query for the Allied count and as you can see it gives me the same thing.
View Replies !
View Related
Count Select
I have this code PHP Code: $result2 = mysql_query ("SELECT COUNT(*) From news group by user asc limit 10"); while($row2 = mysql_fetch_array($result2)) { $un = $row2['user']; echo " .."; echo $un;
View Replies !
View Related
Use Query Result Field As Query Key For New Query..
$query  = "SELECT ProjID, UserID, ProjDesc, file, OrigProj, OrigUser, ProjDate FROM projects"; $result = mysql_query($query); I would like to take the field 'UserID', and utilise it for a Query statement to my users table in my database, to read and fill in information to my table, which looks like this: PHP Code:
View Replies !
View Related
So, SELECT Count(*)... Or Mysql_num_rows(...)
I was running a test on a table with 50000 rows. When I do: $sql = "SELECT * FROM TABLE"; $result = mysql_query($sql); $total = mysql_num_rows(result); I get a 'run out of memory error', (the limit is set low on the test server). But when I do: $sql = "SELECT count(*) as num FROM TABLE"; $result = mysql_query($sql); $result = mysql_fetch_assoc( $result ); $total = $result['num']; Everything works fine. or is $sql = "SELECT FOUND_ROWS() AS num FROM TABLE"; ... even better? So What is the 'preferred' method of getting a row count?
View Replies !
View Related
Problem With Select Count(*)
I have a problem with this query: $link = mysql_connect("localhost", "root", "pw"); $dbsel = mysql_select_db('mydb', $link); $lol='bibi' $sql = "select count(*) as totuur from studres where where dag>= curdate() AND logon='" . $lol . "'"; $result = mysql_query($sql); $row = mysql_fetch_field($result,0); // J'ai essayé avec: $row = mysql_fetch_row($result); $totu=$row[0]; The error is on line: $row = mysql_fetch_field($result,0): Warning: mysql_fetch_field(): supplied argument is not a valid MySQL result resource in c:Inetpubwwwrootmyfile.php on line 74
View Replies !
View Related
Select Count(*) Prblm With PHP & MySQL
I am trying to create a login script that will check to see if it is a valid user. I have tried two different ways but neither one seems to work. Here they are: Attempt #1 $connection = mysql_connect($host,$user,$pswd); $result = mysql_db_query($db,$query,$connection); $cntr = mysql_result($result,0); if($result == 1) This gives me this error: Warning: Supplied argument is not a valid MySQL result resource in /home/lyates/gtstatskeeper-www/corecode/login.php on line 33 Attempt #2 $selectresult = mysql_query($query); if(mysql_num_rows($selectresult) == 1) This gives me this error: Warning: Supplied argument is not a valid MySQL result resource in /home/lyates/gtstatskeeper-www/corecode/login.php on line 33
View Replies !
View Related
Phpgem Error In Select Count ?
I use phpgem to generate forms. it works fine, just one problem with count, I get error messages like this (from the php-script - NOT when i query mysql directly!): SQL query: SELECT COUNT(*) FROM hupoterm WHERE (LOWER(gruppe) LIKE '%e%') Warning: count not found in MySQL result index 2 in sql.php3 on line 255 Could this mean a problem with my table syntax? since 6 days i changed almost all config-parameters in the script, in the table ..... but the error message is still there .
View Replies !
View Related
DISTINCT With COUNT In <select> Field
I am really close to figuring out this and I just need to piece it together. I am creating a data base which I want people to be able to search only for what is actually in there so I want to create a pull down that contains what is in the data base. I have done this and it works great but I would like to go one step further and list the count() next to the item in the select object (pull down) in the form. Code:
View Replies !
View Related
Count Query In Mysql Query
Is it possible to retreave all data from 1 table. and count a number of records where 1 row has the same number as id of the main table? example Table categories id name 1 blah 2 hello Table Threads id name categoryid 1 first 1 2 mid 2 3 noon 1 4 blah 1 5 roar 2.
View Replies !
View Related
Select Result On Empty Table?
can't remember how to do this, doing a select on a db like so. $query = "SELECT user_id FROM tool_user WHERE user_name = '$user_name'"; $query = mysql_query($query) or die("eek"); which will work if there is at least one entry in the database table, however how do i return a result id the table is empty? do i need to use @mysql_query($query) and then something after that?
View Replies !
View Related
Getting A Specific Row From A Mysql_query(SELECT *) Result
I'm selecting a group of records from a table, then, using mysql_fetch_array() and successfully creating a table of results. All fine and no problem. A row in this table is a small portion of the fields from the database record, with the whole record to be displayed on an item details page via a link. My question is: When the user clicks a link to a particular item, which will be identified by the unique id from the record, how do I query the result resource that I already have from the select query to feed that, specifc, record to the details page? I don't want to have to re-query the database using the unique ID if I don't have to. I understand that mysql_fetch_array just advances the array pointer along one.
View Replies !
View Related
Using Query Result To Do Another Query In Loop
I have 2 tables, one called users2 and one called books. Books has a field called UserId, which is the ID of the user that added the book to the database. My problem, is that i need to take this ID from the book table, and use it to get some information from the user table. Here is my code to get the ID from the book table: Code:
View Replies !
View Related
Execute An Update Query Based On The Results Of A Select Query
I'm trying to execute an update query based on the results of a select query... This doesn't work - any ideas? This is the code I'm trying to get to run (it updates all products which are set to arrive by a certain date if that date has arrived or passed - and there are products that should and should not be updated in the db this is being tested with...): Code:
View Replies !
View Related
How Do I Return 10 Results Per Page Using SELECT Statement?
I'm wasn't sure whether to post this under the PHP or under the MySQL section of the forums. Since most people (I'm assuming) who write PHP script, also use MySQL, I thought I'd stick with PHP. I'm rather new to both PHP and MySQL and I'd be very grateful to anyone who can help me solve a problem I'm having at the moment. First, I'll give you an introduction to my little project... I'm developing a database in MySQL that is used to store the song titles of all the MP3s I have on my PC. I've written a basic search feature that works well. The user has 2 options: Either he can type a special "!ALL" "parameter", that I made up, to return all the songs in the database or the user may enter a few characters in the textbox and select "song title" or "artist" etc. from a dropdown to search for a specific song title or artist etc.. Hope you are following so far? Say for example the user presses submit after typing "!ALL" to return all the song titles - there could be hundreds of results. What I want to do now is display 10 results on a page, then on the next page another 10, etc. (like a real search engine!). I've tried doing it with the LIMIT statement, but like I said I'm new to this and discovered that that is not the right way of going about it. My question is this: how would I go about returning 10 matches per page? Do I use hidden form fields? What would the SELECT statement look like? What would the PHP scripting look like to setup the "back" and "forward" navigation between the result for the search?
View Replies !
View Related
Select Count But Exclude Counting Empty Fields?
Hi, any pointers on how I can exclude counting any instances where a field is empty? i.e I have $sql = 'SELECT count(reviewid) as num_times FROM reviews WHERE reviewid =' . $id; I use this to get an average. But some times the average field is empty. So in the above I want to exclude it counting where the average is blank.
View Replies !
View Related
|
TRACKER
