Can Columns From A Left Join Be Assigned To An Array?
I guess this could have gone in the db forums but the question revolves more around how I access some columns as an array from within PHP. Here's what I have got PHP Code:
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
LEFT JOIN, Two Similar Columns
In my left join there are two columns that have the same name.. How can I get their different values when rgetting them through an array?
LEFT JOIN
I am trying to merge results from two tables, but this is something I haven't done so far, so manybe my mistake will look stupid to Pro's. I get the "Warning: mysql_fetch_array(): supplied agrument is not a valid MySQL resource". Here is my code: $result = mysql_query("SELECT * FROM az_mustseethis LEFT JOIN az_articles ON az_mustseethis.art_id = az_articles.art_id WHERE az_articles.category = 'Must See This' ORDER BY art_id DESC LIMIT $from, $max_results",$connect); Instead of adding the latest entry in the "az_mustseethis" table, I want to take it from the "az_articles" table, where it has already been added.
LEFT JOIN (MySQL)
============= The problem: ============= I want to list the users with their country and region (If the user's country has no regions, NULL would be returned). ex: userId = 123 country = USA region = New York userid = 456 country = Japan region = NULL Japon has no regions (in my application) so "NULL" is returned. ============= The Tables: =============
Having Trouble With Left Join
I'm getting NULLs where there shouldn't be. Any help is appreciated. Here are the tables: precinct Field Type Null Key Default Extra id smallint(6) PRI NULL auto_increment precinct_number int(11) 0 precinct_name varchar(20) MUL countydist smallint(6) 0 congdist smallint(6) 0 legdist smallint(6) 0 "precinct_name" is the name of the precinct, not the PCO. That's in the next table. pcolist Field Type Null Key Default Extra id smallint(6) PRI NULL auto_increment precinct int(11) 0 pctname varchar(15) area varchar(4) legdist smallint(6) 0 countydist smallint(6) 0 congdist smallint(6) 0 name varchar(50) email varchar(50) MUL private tinyint(1) 0 type varchar(5) I know there are some duplicates here, which is what I'm trying to solve by this join statement: SELECT king_precinct.precinct_number AS precinct_number, king_precinct.precinct_name AS precinct_name, king_precinct.legdist AS legdist, king_precinct.countydist AS countydist, king_precinct.congdist AS congdist, pcolist.name AS name, pcolist.type AS type FROM king_precinct LEFT JOIN pcolist ON king_precinct.precinct_number = pcolist.precinct ORDER BY precinct_name The problem is that it's not bringing up the name of the PCO in the final result. Can anyone see what I'm doing wrong within the SQL statement? If not, I can post the code I'm using.
Need A Thorough Explanation Of A MySQL Left Join
I have been scouring the internet trying to find a good, solid, visual explanation of a left join. I "kind of" get it but I get confused. If I could see a visual - I get confused by the on clause. I have a database of articles. 1 table is the article itself. Another is article categories, and the last is articles in categories that records the article's ID and the category ID. So I have a left join that someone else wrote that I'd desperately like to understand that looks like this:
LEFT JOIN On Very Large Tables
My database has two tables - films has 10,000 records and reviews has 20,000 records. Whenever I try to list all the films and the count of its reviews, MySQL locks and I have to restart the service. Here's the SQL... SELECT films.*, COUNT(reviews.id) FROM films LEFT JOIN reviews ON films.id = reviews.filmID GROUP BY films.id LIMIT 0, 100; JOIN works but doesn't return records for films with no reviews.
SELECT LEFT JOIN LIMIT WHERE?
I want to have a page with categorized items, and also limit the divide the page into sub pages (with "previous 1 2 3 next" buttons), but I couldn't do it. I have this code: "SELECT products.*,category.name FROM products LEFT JOIN category on product.categorized=category.category_number LIMIT $offset,$limit WHERE category_number='$id'") LIMIT and WHERE doesn't work. But if I delete "LIMIT $offset,$limit", I have a long page.
SQL Query With SELECT DISTINCT And LEFT JOIN
I'm having problems with a SELECT DISTINCT statement with an INNER JOIN. Here is the table structure for my tables: 'locked_accounts' locked_account_id uid 'la_comments' comment_id locked_case_id uid status 'user' uid username And here is my SQL: SELECT DISTINCT l.locked_case_id as locked_case_id, l.uid, u.uid as uid, u.username as username FROM la_comments l LEFT JOIN users u ON l.uid = u.uid where status = 1 What I'm trying to accomplish is getting a display of updated accounts (where status=1) and I want to display their username. In the la_comments table, there might be 50 distinct 'uid's, but I only want to display the distinct uid/username's where the status = 1. Can someone point me in the right direction on this. I've messed with this for a couple of hours and I'm at wits end. Not very bright I am when JOIN statements it comes to.
How Do I Split Into 3 Columns With Array?
The script below works great, however it displays 396 thumbnails. What I would like to do is organize the look and feel better. I want o be able to display the thumbnails in 3 columns rather then scrolling down for ever looking at all the pictures. So how would I make it to break into 3 columns? .....
Sorting A Multidimensional Array On Two Columns
I'm working on some low-tech sort of content managing system. It uses textfiles as a kind of database so the users don't have to install SQL on their server. Now I want to perform some sorting on my files. These are the contents of one of those files. PageName;Template;Menu;Sub; Introductie;1;1;1; Opnemen;1;2;0; Voorbereidingen;1;2;2; Kwaliteit;1;1;0; Blabla;1;2;1; I first read this file into $someArray with file() and then I parse each row with explode() into columns. So $someArray[2][2] contains the value 2 and $someArray[0][2] contains "Sub". I want to sort this array on both Menu and Sub and write it back to the file. It should look like this. PageName;Template;Menu;Sub; Kwaliteit;1;1;0; Introductie;1;1;1; Opnemen;1;2;0; Blabla;1;2;1; Voorbereidingen;1;2;2; I want to make a list of menus and their listed submenus in the proper order, as you can see . What is the clever way to do this? At this moment I use a loop that sorts the submenu in a loop that finds the right menu in a loop that sorts the menus. No, it's not you, I don't understand it myself anymore. That's because I don't understand most of the array principles and functions.
How To Sort An Array, Leaving One Or More Columns Unsorted?
to PHP and have very little knowledge of programming in general, so I may be missing something obvious, but I can't find a solution to the following difficulty in either the PHP manual or Google. I have a tab-delimited text file containing numerous lines of data, which is searched for matches to a query from an HTML form. I want to be able to sort the resulting two-dimensional array of matches by one column only, leaving the other columns unsorted. For example, if three lines match "bar0", foo02 bar01 foobar foobar foo03 bar02 foobar foobar foo01 bar01 foobar foobar should become foo02 bar01 foobar foobar foo01 bar01 foobar foobar foo03 bar02 foobar foobar not foo01 bar01 foobar foobar foo02 bar01 foobar foobar foo03 bar02 foobar foobar The following method works for sorting on the second column, but seems both inelegant and inflexible (especially if I want to sort on the third or fourth column instead: #v+ <?php $data = file("filename.txt"); $sortkey = 0; foreach($data as $line) { // if the line matches a regex, do this: $temp = explode(" ", rtrim($line," ")); $newdata[]= array($temp[1], $sortkey, $temp[0], $temp[2], $temp[3]); $sortkey++; } if(sizeof($newdata) > 0) { sort($newdata); foreach($newdata as $line) { echo $line[2]." ".$line[0]." ".$line[3]." ".$line[4]." "; } } ?> #v- Is there a less clumsy way of doing this kind of thing without having to use a "real" database? I've tried all the inbuilt array-sorting functions, without any success so far.
Drop Down With Assigned Value From Database
I have a database that has customers in it. When they purchase things from me, each purchase is either "online" or "onsite". So a selection must be made as either "online" or "onsite" when the item is entered into the database. Often, I have to update this value for an item later on from "online" to "onsite" and vice versa. Is there anyway to populate the "Type" field with whatever the value is in the datablse for this particular item purchased, then allow someone to select "online" or "onsite" from the dropdown if it needs to be edited? Code:
Left
how can I change the lenght of an value in basuc it's called LEFT or RIGHT and MID . I read the filenames but I want to strip the extension from the filename. ======== SCRIPT ============== <table style="font-family: tahoma,helvetica,arial; font-size: 12px; font-weight: bold; color: #000000"> <?php $handle=opendir('news'); while (false!==($file = readdir($handle))) { if ($file != "." && $file != "..") { echo " <TR><TD>$file "; } } closedir($handle); ?> </body> </html> =======RESULT ======== 200503290000.txt 200503300000.txt 200503310000.txt 200504010000.txt =======But I want this result : ====== 200503290000 200503300000 200503310000 200504010000 ===================
MYSQL LEFT With PHP WHILE
I am trying to get the following to work, but end up only getting a long drop-down list of blank item. In MySQL alone, the LEFT function works fine, but not when I try to push it through a WHILE loop in PHP. Any help is much appreciated. $sql = "SELECT LEFT(MFG_LONGNAME,20) FROM MLIST ORDER BY MFG_LONGNAME"; $sql_result = mysql_query($sql,$connection) or die ("Couldn't execute query."); echo "<td valign=middle align=middle> <font face=arial size=1> <SELECT NAME="manufacturer"> <Option Value="">All Manufacturers</option>"; while ($row = mysql_fetch_array($sql_result)) { $manufacturer = $row["MFG_LONGNAME"]; echo "<option value="$manufacturer">$manufacturer</option>"; }
Pulling Right/Left Characters
How do I pull a specified number of characters either from the right or left? For example, the output is 123456 and I want to pull, separately, the right 3 characters (result: 456) and the left 2 characters (result: 12).
Simulation Of Left Mouse Click
I was looking for this script for some time now But still didmf fined a salution. I need a script that simulates a left mouse click on specified area of the screen when the page is loaded.
PHP Not Compiling - I'm Loosing What's Left Of My Hair Over This!
I'm trying to compile PHP 4.4.1 on RH E3 ES. I need to enable zlib support but when I do PHP does not compile without errors. Here's what I did. make clean; ../configure --with-apxs2=/usr/sbin/apxs --with-mysql=/usr --with-config-file-path=/etc --enable-mbstring=all --with-zlib=/usr make; make: *** [libphp4.la] Error 1 If I run configure without --with-zlib=/usr it compiles just fine. So it is definitly zlib that's causing the error. What's weird though is that the error seems to come from mysql! The full error log is included below. I'm using MySQL 4.1 compiled from source. (It works fine.) /usr/lib/mysql/libz.a(adler32.o)(.text+0x0): In function `adler32': : multiple definition of `adler32' /usr/lib/mysql/libz.a(adler32.o)(.text+0x0): first defined here /usr/lib/mysql/libz.a(adler32.o)(.text+0x250): In function `adler32_combine': : multiple definition of `adler32_combine' /usr/lib/mysql/libz.a(adler32.o)(.text+0x250): first defined here /usr/lib/mysql/libz.a(compress.o)(.text+0x0): In function `compress2': : multiple definition of `compress2' /usr/lib/mysql/libz.a(compress.o)(.text+0x0): first defined here /usr/lib/mysql/libz.a(compress.o)(.text+0xb0): In function `compress': : multiple definition of `compress' /usr/lib/mysql/libz.a(compress.o)(.text+0xb0): first defined here /usr/lib/mysql/libz.a(compress.o)(.text+0xf0): In function `compressBound': : multiple definition of `compressBound' /usr/lib/mysql/libz.a(compress.o)(.text+0xf0): first defined here /usr/lib/mysql/libz.a(crc32.o)(.text+0x0): In function `get_crc_table': : multiple definition of `get_crc_table' /usr/lib/mysql/libz.a(crc32.o)(.text+0x0): first defined here /usr/lib/mysql/libz.a(crc32.o)(.text+0x10): In function `crc32': : multiple definition of `crc32' /usr/lib/mysql/libz.a(crc32.o)(.text+0x10): first defined here /usr/lib/mysql/libz.a(crc32.o)(.text+0x690): In function `crc32_combine': : multiple definition of `crc32_combine' /usr/lib/mysql/libz.a(crc32.o)(.text+0x690): first defined here /usr/lib/mysql/libz.a(deflate.o)(.rodata+0x0): multiple definition of `deflate_copyright' /usr/lib/mysql/libz.a(deflate.o)(.rodata+0x0): first defined here /usr/lib/mysql/libz.a(deflate.o)(.text+0x0): In function `deflateInit_': : multiple definition of `deflateInit_' /usr/lib/mysql/libz.a(deflate.o)(.text+0x0): first defined here /usr/lib/mysql/libz.a(deflate.o)(.text+0x50): In function `deflateInit2_': : multiple definition of `deflateInit2_' /usr/lib/mysql/libz.a(deflate.o)(.text+0x50): first defined here /usr/lib/mysql/libz.a(deflate.o)(.text+0x460): In function `deflateReset': : multiple definition of `deflateReset' /usr/lib/mysql/libz.a(deflate.o)(.text+0x460): first defined here /usr/lib/mysql/libz.a(deflate.o)(.text+0x1240): In function `deflateEnd': : multiple definition of `deflateEnd' /usr/lib/mysql/libz.a(deflate.o)(.text+0x1240): first defined here /usr/lib/mysql/libz.a(deflate.o)(.text+0x300): In function `deflateSetDictionary':
Reading Left And Right Quotes From File.
I am trying to read in the content of a file with the php file() function and when I do then all the left and right quotes “” and ‘’ are conveted to odd characters. so this: Downtown is ‘sole focus’ becomes this: Downtown is Ôsole focusÕ�� I read the file like this $array = file($_FILES['tmp_name']); $headline = $array[0]; echo $headline;
Make Text String Read From Right To Left In Imagettftext() Function
I want to write a text string from right to left instead of from left to right with the imagettftext (); function I read in teh manual that the angle variable controls this, it says that 0 angle means left to right, so I tried 180, 360 but nothing happens What angle do I need to put it to get it to write it right to left I am writing a hebrew text string with a font.ttf that supports hebrew characters <?php $white = imagecolorallocate($background, 255, 255, 255); $fontfile = "davidtr.ttf"; $string = "מחלדגכ"; imagettftext($background, 12, 360, 3, 17, $white, $fontfile, $string); ?>
Intermittent Session Errors, Write Failed: No Space Left On Device
I have written a series of php scripts which access a mySQL database and makes use of session variables. Just in the past two days I have intermittently been receiving the following error messages: Warning: Unknown(): write failed: No space left on device (28) in Unknown on line 0 Warning: Unknown(): Failed to write session data (files). Please verify that the current setting of session.save_path is correct (/tmp) in Unknown on line 0 I have not changed the code relating to the session variables in several days. This is on a shared server. The server is a linux server running php 4.3.9 and Apache 1.3.31.
How To Use Join?
I have to tables and I want to display the most recent row of both of them using a field, 'date', which I have in both. So I just want to be able to put two tables into one query.
Getting A Value From A Join
I have 3 tables which each contain a field named discount table1.discount table2.discount table3.discount I would like to do a join on the 3 tables and get the highest discount value of the three where table1.item = 'some number' How should I structure the query?
Sql Join
This is a simple join but I am having trouble with it. I feel like such an idiot. I start with this select statement, which works fine: $query = "SELECT a.auction, a.winner, b.id FROM PHPAUCTIONXL_winners a, PHPAUCTIONXL_auctions b WHERE a.auction=b.id"; Now I only want the results from auctions with ids that are not listed in the 'archived' field of the PHPAUCTIONXL_archived table. So I added another table and an AND to the SELECT statement. Now I don't understand why it doesn't show the auctions which are not archived. $query = "SELECT a.auction, a.winner, b.id FROM PHPAUCTIONXL_winners a, PHPAUCTIONXL_auctions b, PHPAUCTIONXL_archived c WHERE a.auction=b.id AND a.auction !=c.archived";
Using Join
I have 3 tables a, b, and c. I know what is in table a and I want the related info from table b and a. Is there some what to do that in one query? Code:
SQL INNER JOIN
I have this SQL statement; $confirmDetails = mysql_query("SELECT * FROM orders INNER JOIN reference ON orders.id = reference.box WHERE reference.refNum = $getRefNum"); ...which works, or did work fine. Today I have a strange problem... ... it will only fetch the 6 most recent entries from the database. Does anyone have any idea what this could be? I'm guessing its a wierd MySQL issue but I'm a bit in the dark on these sort of things as I am new to it all really.
Sortable Columns
Hi there, I am new to this Forums...I am currently learning PHP and I was wondering if any of you guys knows how to make columsn in a table sortable. What would I have to change in the code? Thanks!
UPDATE Columns With PHP
So you know I'm going through Kevins book. I'm updating data in a test table. I'm adding names to each joke, say I have 7 jokes and 3 of them go to the same person. I can update each one seperately but I can't find a way of bulk updating. In this case it's not a problem. I've looked though Kevins book and a couple of others but can't find anything. Tried several sites to but I may just be searching wrong. I can do this: mysql> UPDATE Jokes SET AID="1" WHERE ID=2; but I'd like to update WHERE ID equals 2,3,4,5 all at once.
Columns In Html
I am trying to make a table with two columns. I am really close. The problem is the table's first row has three TD's. ie one two there four five six seven PHP Code:
Sortable Columns
Does anybody know here how to make columns in a table sortable? I know that with a sql query I can accomplish that. PHP Code:
Grouping The Columns
Let's say I have a very simple Table: +---------------------+ | id | name | level | +---------------------+ | 1 | Adam | 1 | | 2 | Jack | 7 | | 3 | Bill | 1 | | 4 | Pete | 3 | | 5 | Ross | 7 | +---------------------+ but I want the PHP to output their id's and names, but group their Levels together like so: Level 1: Adam (id: 1) Bill (id: 3) Level 3: Pete (id: 4) Level 7: Jack (id: 2) Ross (id: 5) And then afterwards, If I create someone with a new level, PHP will automatically create a new heading (e.g. Level 10) and put the corresponding names under it. How should I approach this? Should my PHP contain two "while"s? Or do I use the SQL <group by> function?
Displaying Columns With A Value
what is the most efficient way to display column names from a database that only have the value '1' in its fields?? I cant seem to work it out, could someone give me a code example??
Creating Columns
I've been using this code to split records from a db into columns when it displays the data. Items are shown with 3 on a row and then a new row starts. It works really well but if ever there is only one item on a row it causes me a problem in this instance. I have far left and far right columns which display a background image, which is a border. The problem is this only works if there are three columns visible in a row. Is it possible to manipulate the following code so that there are always three columns present, they're just empty if there's no more records to fill them? Code:
Join() Problems
What I am trying to do is capture the HTML output from a PHP generated script, and place it into a .html file. I am really close to having it complete but I am getting an error that I can not figure out. Any help would be MUCH appreciated! Here is my code (file is called mirror.php3):
Problem With JOIN
I am trying to work with JOIN. But it's not showing output. I send it with code. is there any error on my code? <?php $db_connect = mysql_connect('localhost', 'root', ''); $db_select = mysql_select_db(ཚk', $db_connect); $sql = "SELECT t1.name1, t2.dist2 FROM join1 t1, join2 t2 WHERE t1.nation = t2.nation"; //$sql = "SELECT name1,dist1 FROM join1 WHERE nation = 'india'"; $query = mysql_query($sql) or die(mysql_error()); while($row = mysql_fetch_array($query, MYSQL_ASSOC)) { echo $row['t1.name1'] . "<br>"; } ?>
Mysql - SUM And INNER JOIN
Can anyone help with this query? Here is what I am trying to do: mysql_query("SELECT code.* FROM code INNER JOIN ratings ON code.id = ratings.codeid WHERE SUM(ratings.rating) >= 50 ORDER BY code.id DESC"); Obviously this won't work because you cant use SUM in the WHERE clause, but you can see what I am trying to do. Any ideas?
Table Join
I used this SQl query to join two table that have usedID and msepcdp userid in common. "SELECT * FROM users,msepcdp WHERE users.ID = msepcdp.userid AND msepcdp.type LIKE 'music" Wheni i try to echo the the fields using: "$query2 = "SELECT * FROM users,msepcdp WHERE users.ID = msepcdp.userid AND msepcdp.type LIKE 'music'"; Code:
SQL Query - INNER JOIN ?
I have 2 tables, "Properties" and "PropertyLocation" Properties contains a field that refers to a field in PropertyLocation. I create a resultset from my querie, and then I display it. This works well, but I cannot show the field from PropertyLocation. This is the query I tried: Code:
SQL JOIN Problem!
I've got a problem with my SQL JOIN on my database stored on UNIX machine by Oléane !!!! (my SQL query is stored in php page) Here is my join's type : WHILE 'table1'.id = 'table2'.num_id Have you got an idea?
Join 5 Tables
i need to join together 5 tables in a psql database i have made. the problem is that i cant seem to find the correct sql to perform the join. the tables i have are group, person, contacts, movements and treatments. group and person are connected by a group_id, established in group, each entry in the person table also has a group_id. the other tables are connected by person_number, established in person and a foreign key in all the other tables.
Dates Join
how can i join them: <input type="text" name="dd">15 <input type="text" name="mm">03 <input type="text" name="yyyy">2002 to return yyyy-mm-dd 2002-03-15.
Concat In A Join?
I think the following statement .... $ml_collect='SELECT *, DATE(CONCAT(field1, field2)) AS thedate FROM ml_lopp LEFT JOIN scfmforening ON (scfmforening.scfmnum = ml_lopp.scfmnum) LEFT JOIN ml_tidplats ON (ml_tidplats.loppnum = ml_lopp.loppnum) ORDER BY thedate' .....would work if "field1" and "field2" were in the table "ml_lopp" However, "field1" and "field2" are in the table "ml_tidplats" and accessed by "LEFT JOIN". So, how do I use "DATE(CONCAT(field1, field2))" in this case?
Table Join
The table WALKS lists walks where some records have photos available. To save searching through 750+ records to find those with photos, I have another table ALBUM with walks.walk_id listed in the column walk. The MySQL query that follows isn’t working. What am I doing wrong? Code:
Join 2 Files
I am trying to create a self extracting zip file ( on my Linux web server ) that will ultimately run on a windows OS. Research to date indicates that I need to create a zip file and then join it together with a win32 based sfx header stub ( which I have ). The resulting file can then be run on a windows OS, with the sfx stub running first and extracting the data from the zip file. Question is : how can I join the 2 files together into a new file ?
|
TRACKER
